Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to Improve my Dax/PowerQuery by avoiding hard-coding

Hi I have a table as follows CustID GroupID CustType Amount Group_CustType_Concat 1 1 Intermediate 10 1Intermediate  1 1 Intermediate  20 1Intermediate  2 3 Regular 30 3R...
Need Help
Show and Tell
Tips and Tricks
lbendlin's avatar
lbendlin
Super User
4 years ago

You may need to rethink your "what-if"  UX.  It is not possible to dynamically add/remove visuals in Power BI, and Small Multiples are not supported in "What-If"  scenarios.  Have you thought about using the filter panel instead?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi lbendlin ,

 

The user viewing the dashboard needs to be able to change the values of the what-if slicers and that should only impact the sum of 'Amount' of that specific Group_CustType_Concat. So will not be able to use the filter panel for this. Also I am not trying to add or remove visuals, just trying to multiply the value of slicer to its respective concatinated group. For example this the measure to calculate the value when user changes the value of the 'Intermediate' slicer of Group 1:

 

1Intermediate= Calculate(sum('Table'[amount]),'Table'[Group_CustType_Concat]=="1Intermediate") * '1Intermediate'[1Intermediate Value]

 

This is repeated for all unique Group_CustType_Concat and then added together using switch statement based on each custType.

 

Linechart = var type=IF(ISBLANK(SELECTEDVALUE('Table'[CustType])),TRUE,SELECTEDVALUE('Table'[CustType]))
return switch(type,
"Intermediate", [1Intermediate] + [2Intermediate] + [3Intermediate]
,"Regular", [1Regular] + [2Regular]+ [3Regular]
 
Is there a simpler, more automated way of doing this?