Forum Discussion

amitnarkar3's avatar
amitnarkar3
Icon for Helper I rankHelper I
9 years ago

ISFILTERED to work with null

HI Team,

 

I have 3 slicers Level3 --> Level 2 --> Level 1 as hiearchy.

 

I have done some calculations using following formula.

 

Calculate = if(ISFILTERED('Hierarchy'[Level1]),CALCULATE(AVERAGE(Table1[value]),USERELATIONSHIP(Table1[Date_time_value],DimDate[Date_time_value])),if(ISFILTERED('Hierarchy'[Level2]),CALCULATE(average(Table2[value]),USERELATIONSHIP(Table2[Date_time_value],DimDate[Date_time_value])),if(ISFILTERED('Hierarchy'[Level3]),CALCULATE(average(Table3[value]),USERELATIONSHIP(Table3[Date_time_value],DimDate[Date_time_value])),CALCULATE(average(Table3[HOURLY_WUE]),USERELATIONSHIP(Table3[Date_time_value],DimDate[Date_time_value])))))

 

In the hierarchy level2 has NULL values which I have renamed as shared

i.e. There are level1 items which directly roll up to level3 but we need to show Level2 (NULL) as shared in this case

 

My objective is to show blank whenever I click on shared in level 2 but when I choose underlying Level1 items for shared Level2 I should get correct result.

I tried enforcing blank using if else statement but I get blank even when I have selected Level1 item under NULL or shared Level2.

 

I would appreciate any suggestions to take care of this.

Need Help

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    can you post a sample of the hierarchy table ?

     

    Calculate =
IF (
    ISFILTERED ( 'Hierarchy'[Level1] ),
    CALCULATE (
        AVERAGE ( Table1[value] ),
        USERELATIONSHIP ( Table1[Date_time_value], DimDate[Date_time_value] )
    ),
    IF (
        ISFILTERED ( 'Hierarchy'[Level2] ),
        CALCULATE (
            AVERAGE ( Table2[value] ),
            USERELATIONSHIP ( Table2[Date_time_value], DimDate[Date_time_value] )
        ),
        IF (
            ISFILTERED ( 'Hierarchy'[Level3] ),
            CALCULATE (
                AVERAGE ( Table3[value] ),
                USERELATIONSHIP ( Table3[Date_time_value], DimDate[Date_time_value] )
            ),
            CALCULATE (
                AVERAGE ( Table3[HOURLY_WUE] ),
                USERELATIONSHIP ( Table3[Date_time_value], DimDate[Date_time_value] )
            )
        )
    )
)
      • amitnarkar3's avatar
        amitnarkar3
        Icon for Helper I rankHelper I
        9 years ago

        Hi Sean,

         

        As of now I managed with below measure.

         

        Calculate =
        IF (
        ISFILTERED ( 'Hierarchy'[Level1] ),
        CALCULATE (
        AVERAGE ( Table1[value] ),
        USERELATIONSHIP ( Table1[Date_time_value], DimDate[Date_time_value] )
        ),
        IF (
        AND(ISFILTERED ( 'Hierarchy'[Level2] ),min('Hierarchy'[Level2])="Shared"),
        CALCULATE (
        AVERAGE ( Table1[value] ),
        USERELATIONSHIP ( Table1[Date_time_value], DimDate[Date_time_value] )
        ),

        IF (
        AND(ISFILTERED ( 'Hierarchy'[Level2] ),min('Hierarchy'[Level2])<>"Shared"),
        CALCULATE (
        AVERAGE ( Table2[value] ),
        USERELATIONSHIP ( Table2[Date_time_value], DimDate[Date_time_value] )
        ),
        IF (
        ISFILTERED ( 'Hierarchy'[Level3] ),
        CALCULATE (
        AVERAGE ( Table3[value] ),
        USERELATIONSHIP ( Table3[Date_time_value], DimDate[Date_time_value] )
        ),
        CALCULATE (
        AVERAGE ( Table3[value] ),
        USERELATIONSHIP ( Table3[Date_time_value], DimDate[Date_time_value] )
        )
        )
        )
        )

         

        But I strongly believe it is likely to give me trouble specially  logic around min(shared)

        Do you have any suggestions?