ISFILTERED to work with null

[amitnarkar3]

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.

[Sean]

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]

[amitnarkar3]

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?

[v-huizhn-msft]

Hi @amitnarkar3,

For your requriement, it's still confusing. Could you please descriobe it in detail based on your given sample data?

Best Regards,
Angelia

[amitnarkar3]

@v-huizhn-msft

Apologies I did not provide enough details earlier. I need to get values from lower nodes in the hierarchy if I have null values in Sublocation.

So if you look at my sample data. There is no sublocation for sydney. The moment I select NULL or Shared(NULL renamed as Shared)
I should get result as blank. If I selewct Washington I should get result as some aggregate function.
Similarly When I select Sydney by default NULL will get selected as parent but in this case instead of blank I need to show some aggregation.