DAX challenge (nested if or switch is not working)

[Maximous89]

Hello power bi sharks,

i have a challenge power bi dax measure, it's quite simple to under stand acctully 

i have a measure that has many variable measures inside it, and i want this measure to return the value based on the column that user is going to choose and add it inside the visual, so if the user choose column A  the measure is going to return A, but wait it's not that simple yet, 

the user could choose coulmn A and then column B in the same visual as HIER for example in matrix table, in the rows, could choose first column A then column B then Column C and the same measue suppose to return on column level A value (A) and then column level B value (B) and then column level C value (C) so on....., 

i tried to use switch first, because it's not working, why?! because at the first condtion met, the switch is stop, ignoring other values to be returned, even nested if have the same issue, 

it not if or switch that should we use i think,

it's something like find or search 

because i have to say to dax if this column inscope use this, and if this column inscope use this... so one, without stopping..

just like below the user could add any number of columns below, and the measure should return the correct value on this level...

could you please help me?!!

below measure is wrong, because it stops when coulmn level_1_desc is met, but what about the others?!! even nested if stops at first condition met?!

SWITCH(TRUE(),
                ISINSCOPE( HIER[LEVEL_1_DESC] ), _sum_of_col_H1_DRO,
                ISINSCOPE( HIER[LEVEL_2_DESC] ), _sum_of_col_H1_DRO_CAT,
                ISINSCOPE( HIER[LEVEL_3_DESC] ), _sum_of_col_H1_PCK,
                ISINSCOPE( HIER[LEVEL_4_DESC] ), _sum_of_col_H1_SIZE,
                ISINSCOPE( HIER[LEVEL_5_DESC] ), _sum_of_col_H1_MANUFACTURER,
                ISINSCOPE( HIER[LEVEL_6_DESC] ), _sum_of_col_H1_BRAND,
                ISINSCOPE( HIER[LEVEL_7_DESC] ), _sum_of_col_H1_ITEM,
                _sum_of_col_H1_DRO
            )

[jothimeena]

you shoud provide condition right like greater or smaller if it numerical ,if it is category yu should provide contains .not sure am i gave the solution to yu .

[v-echaithra]

Hi @Maximous89 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, please provide the sample data, we are happy to help.

Thank you.

[v-echaithra]

Hi @Maximous89 ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support please provide sample data and please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[v-echaithra]

Hi @Maximous89 ,

Thank you @tharunkumarRTK  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required, please provide a sample data. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[tamerj1]

@Maximous89 

How fo you select the columns? Is it a field parameter slicer? Why do you need to have them listed in a hierarchy? Would be great to have some screenshots. 

[Maximous89]

what if the user change the hier in the visual?!!!

[v-echaithra · Accepted solution]

Hi @Maximous89 ,

Thank you for your update.
Could you please share a sample dataset that reflects your scenario? This will help us accurately reproduce your requirement and provide the most possible solution.

Warm Regards,
Chaithra E.

[tharunkumarRTK]

@Maximous89 

if there is a order which your user follows to add columns to the hierarchy then you can follow the below approach that is checking the deepest hierarchy first then the higer levels. 

SWITCH(TRUE(),
    ISINSCOPE(HIER[LEVEL_7_DESC]), _sum_of_col_H1_ITEM,
    ISINSCOPE(HIER[LEVEL_6_DESC]), _sum_of_col_H1_BRAND,
    ISINSCOPE(HIER[LEVEL_5_DESC]), _sum_of_col_H1_MANUFACTURER,
    ISINSCOPE(HIER[LEVEL_4_DESC]), _sum_of_col_H1_SIZE,
    ISINSCOPE(HIER[LEVEL_3_DESC]), _sum_of_col_H1_PCK,
    ISINSCOPE(HIER[LEVEL_2_DESC]), _sum_of_col_H1_DRO_CAT,
    ISINSCOPE(HIER[LEVEL_1_DESC]), _sum_of_col_H1_DRO,
    _sum_of_col_H1_DRO
)

this will check the deepest level first and then the higher levels

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Maximous89]

@tharunkumarRTK 

yes, but what if condition 

 ISINSCOPE(HIER[LEVEL_7_DESC]), _sum_of_col_H1_ITEM,

 has been met, then the measure will no longer return level_1_desc any more and it will stop again.

[tharunkumarRTK]

Like I mentioned, if there is specific order for your row hierarchies like 1 being the top hierarchy and 7 being the lowest hierarchy then the syntax I shared with work.

Because in level 1, only Isinscope(level 1) will be true and remaining will be false, since we kept level 1 condition as a last condition, switch will try to check till the first true value that is level 1 condition.

similarly other levels.

if there is no order for the hierachy then this syntax wont work. 

[Maximous89]

@tharunkumarRTK 

there is no order for the hierachy, sometime user can add level_2 then level 3, or level_2 then level_1, or level_7 then level_5 so on.. he might also use three or four columns ....

[tharunkumarRTK]

@Maximous89 

If the row hierarchies are DYNAMIC then

As far as I know, there are no dax functions to capture the column name of the current row hierarchy in a matrix visual. Even in visual calculations you can dynamically capture the parent total value but you cannot capture the child hierachy column name or parent hierarchy column name.

I would sugget you to define few predefined hierarchy orders, lets say 3 or 4 orders which are meaning ful for your report context

Create those many matrix visuals and keep them in different bookmarks. Provide a bookmark navigator option to your users so that they can switch in between them