Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to use Dimensions in if else statement in calculated measure

  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Default How to use Dimensions in if else statement in calculated measure

    My requirement is to use a dimension value as a condition in calculated measure. The scenario is:

    Dimension is:
    <Dimension type="StandardDimension" visible="true" highCardinality="false" name="QAP_FLAG" caption="As Of Date Policy Transaction Dimensions" description="QAP_FLAG">
    <Hierarchy name="QAP Flag" visible="true" hasAll="true" caption="As Of Date Policy Transaction Dimensions" description="QAP Flag">
    <Level name="QAP Flag" visible="false" column="Tran_QAP_Flag" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" description="QAP Flag">
    </Level>
    </Hierarchy>
    </Dimension>

    The formula I am using in calculated measure is:
    IIF([QAP_FLAG].[QAP Flag].[MEMBERS]='Q',Calc_measure_1,Calc_measure_2)

    But it is not working. Calc_measure_2 is showing correct values, but Calc_measure_1 never returns its true value. Please suggest what I am doing incorrect here.

  2. #2
    Join Date
    Sep 2013
    Posts
    17

    Default

    You Calculated Member : IIF([QAP_FLAG].[QAP Flag].[MEMBERS]='Q',Calc_measure_1,Calc_measure_2) seems to be incorrect.

    Dimension.level.Members always retuns a Set and you are equating a Set to a String value. I think your usage should be like
    IIF([QAP_FLAG].[QAP Flag].CurrentMember='Q',Calc_measure_1,Calc_measure_2)

  3. #3
    Join Date
    Aug 2013
    Posts
    2

    Default

    Ok, I got the correct syntax for this:
    IIF([QAP_FLAG].[QAP Flag].CurrentMember.Name='Q',Calc_measure_1,Calc_measure_2)

    But still, the result set is returning values for
    Calc_measure_2 even when the condition QAP Flag='Q' holds true. I am not able to pin down the error. Please help.
    Last edited by shikha.sid; 05-12-2014 at 03:42 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.