Forum Discussion
Helper IDAX challenge (nested if or switch is not working)
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
)
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.
13 Replies
- tharunkumarRTK
Super User
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!
Maximous89yes, 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.
- tamerj1
Community Champion
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-echaithraCommunity Support
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.
- 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. - 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 ,
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.
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 .
Alex_SawdoResolver II
Realistically, a field parameter would be a better implementation of this situation than a DAX Switch statement. For the field parameter, you would select/add every measure you would like a user to select from (like what you have in the switch statement) and use this as a slicer on the page. Also with a field parameter, you can select multiple values and each value will appear and not get ignored like a Switch statement would.
