Forum Discussion
Dynamic Filter with Dynamic Measure Selection in Report Matrix
I think a better approach is to choose one pattern and stick with it, and calculation groups are probably the better option here.
What you’re seeing now actually makes sense once the calculation group is in place, it applies its logic to whatever measure is in the matrix.
That’s why the original dynamic SWITCH measures feel pointless they’re effectively being bypassed, and that’s expected behavior.A simpler setup would be to keep very basic measures that do only one thing, for example:
Base Revenue := SUM ( Sales[Revenue] ) Base Attainment := DIVIDE ( SUM ( Sales[Actual] ), SUM ( Sales[Target] ) )
Then let a calculation group handle the choice between Revenue and Attainment using SELECTEDMEASURE(), including the correct formatting for currency versus percent.
In the matrix you only need one base measure, and the slicer on the calculation group choose what the user sees.I think this should be better mainly because it avoids duplicated logic and fixes the formatting issue in a more natural way, even though your current solution does technically work.
pacificnwp , You want to switch the format, you have to dynamic string formatting and almost the same Switch you have right format
https://www.sqlbi.com/articles/introducing-dynamic-format-strings-for-dax-measures/
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
In the format part measure, something like this, chnage as per need
SWITCH(
TRUE(),
[User Metric Selection] = "Revenue",
"$#,##0.00;($#,##0.00)",
[User Metric Selection] = "Attainment",
"0.0%",
"#,##0"
)
Thanks - I proceeded with the calculation group method, however I don't like that it removed implicit measures from my data set. I then tried to implement your solution. I was able to get it to work for my matrix, however the formatting converted the values to text, which didn't work for my line chart. I have both a line chart and a matrix in my report that leverage these fields.
Is it possible to do this while preserving the data types?