IF Function not working

[Georg]

Hi Experts,

I try an If function and it seems I am missing a very basic point.

The IF function is in the calculated column 'FilteredLevel' and referes to a calculated measure:

 

Since LevelH1 is not "L5" I expext to get "NO". The formula is not working as expected. What am I missing?

kind regards,

[Dog]

Hi @Georg

what's the formula behind measure [LevelH1]?

Thanks

[Georg]

Hi,

the formula is

LevelH1:= VAR Lvl=
IF ( ISFILTERED ( [Level5] );
                    "L5";
                   IF(ISFILTERED(ProfitCenterH1[Level4]);
"L4";"XXX"))
return Lvl

I have a hierarchy with 8 levels. What I want to achieve is a column with the childs of the selected parent. E.G. if Level5 is selected the calculated column should display all members of Level6.

kind regards,

Georg

[v-huizhn-msft]

Hi @Georg,

You use ISFILTERED function in the LevelH1 measure, how do you judge if there is a filter? I create the simple data, and use the similar measure, it always return XXX. Could you please share your .pbix for further analysis?

Best Regards,
Angelia

[Dog]

Hi George, 

if you are just looking at this in raw data then it's not currently filtered - what results do you get if you add the columns into a table within powerbi, ensuring you pull through the columns you are checking to see if they are filtered?

Dog

[Georg]

Hi,

while LevelH1 is changing according to the selected level, FilteredLevel remains unchanged.

kind regards,

Georg

[Dog]

Hi George. 

you'll probably need to change the filteredlevel to a measure as well as calculated columns are only calculated the once. they do not get re calculated by filter changes. 

Dog