Multiple nested IF statements to display items in a hierarchy in a card

[Anonymous]

Hi,

We have 3 levels of an organisational hierarchy (Level_2, Level_3, Level_4 - see table 2 below) the names of which we want displaying in a card after users have selected any item in any of the three levels of the hierarchy. This way once they've made the selection and can no longer see the hierarchy slicers (we place the slicers in a hide-able pane to save space), there's a card referencing what levels the data is showing. We have been able to make this work when there are two levels to the hierarchy, but my IF statement logic and field name recall functions are failing me with three levels.

There's also an extra complication in that while most level_4s report straight into a level_2, a small number report into a level_3. The card needs to display the names as such in these examples:

Table 1: selection examples

User selects	Card displays
Level_2 1A	1A
Level_4 One	1A - One
Level_4 Five	1B - Five
Level_4 Seven	1C - 2A - Seven
Level_3 2B	1C - 2B

Table 2: simplified hierarchy

Level_2	Level_3	Level_4
1A		One
1A		Two
1A		Three
1B		Four
1B		Five
1B		Six
1C	2A	Seven
1C	2B	Eight
1C		Nine

The DAX I've played with so far is below is attached, but I've got to the point where I can't follow or create the correct nested IF logic, and also not getting my nested CONCATENATEs correct.

Any help would be greatly appreciated.

[v-frfei-msft]

Hi @Anonymous ,

Please create a measure as below.

Measure = 
VAR crL2 =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Level_2] ) )
VAR c =
    COUNTROWS ( 'Table' )
RETURN
    IF (
        crL2 = c,
        MAX ( 'Table'[Level_2] ),
        MAX ( 'Table'[Level_2] ) & "-"
            & MAX ( 'Table'[Level_3] ) & "-"
            & MAX ( 'Table'[Level_4] )
    )

For more details, please check the pbix as attached.

[Anonymous]

I have just gotten very close to what I need with the following DAX (apologies, I don't know how to enter this in the same formatted box as you posted your solution in):

Selection test =

"Current Selection: " &

IF(DISTINCTCOUNT(DIM_Hierarchy[Hierarchy_Level_2_Name]) >1,"ALL",MAX(DIM_Hierarchy[Hierarchy_Level_2_Name])) &

IF(DISTINCTCOUNT(DIM_Hierarchy[Hierarchy_Level_3_Short_Name]) > 1, "", " - " & MAX(DIM_Hierarchy[Hierarchy_Level_3_Short_Name])) &

IF(DISTINCTCOUNT(DIM_Hierarchy[Hierarchy_Level_4_Short_Name]) > 1, "", " - " & MAX(DIM_Hierarchy[Hierarchy_Level_4_Short_Name]))

It isn't perfect as it's inserting " - " when no level_3 is selected, so it's a logic issue, but it does what I need it to minus this small issue.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please update the measure as below.

Measure = 
VAR crL2 =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Level_2] ) )
VAR crl3 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Level_2], 'Table'[Level_3] )
    )
VAR c =
    COUNTROWS ( 'Table' )
RETURN
    IF (
        crL2 = c,
        MAX ( 'Table'[Level_2] ),
        IF (
            crL2 = crl3
                <> FALSE ()
                && MAX ( 'Table'[Level_3] ) <> BLANK (),
            MAX ( 'Table'[Level_2] ) & "-"
                & MAX ( 'Table'[Level_3] ) & "-"
                & MAX ( 'Table'[Level_4] ),
            MAX ( 'Table'[Level_2] ) & "-"
                & MAX ( 'Table'[Level_4] )
        )
    )

 

[Anonymous]

Hi,

Thank you for the reply!

Your suggestion is nearly perfect, other than the fact that (and I didn't make this clear) there are more than one level_4 entities under level_3 2A and 2B, so where you select a level_3 entity in your solution, it is automatically selecting 'Seven' at level_4. We need to be able to select 2A alone, and this will aggregate the performance of all the level_4 entities under level_3.

Also, for some reason this isn't working in my actual report. I'm unable to share the file however as it's connected to an analysis services tabular model. I should be able to work with your solution though and make it work in our model.