Forum Discussion
Switch/IF error when expanding a matrix
I think I have found a work around, my data sources are mixed with some pulling from Power BI semantic models others SQL. The report I was was working on had a local model which I used to create the dax table for the measures, which I think glitched out the other source connections. When replicating the issue in the online semantic models the tables worked fine with the code left unchanged, so I think it just didn't register all the changes locally.
Super User
At the lowest level of the hierarchy, the metrics column is expanded. In that case, SELECTEDVALUE returns a value because each row corresponds to a single metric. When the hierarchy isn’t expanded, the metrics column contains multiple values (e.g., Net and Gross), so SELECTEDVALUE evaluates to blank.
Since BLANK() is not equal to "Net", the condition
SELECTEDVALUE(MetricsTable[Metric]) = "Net"evaluates to FALSE(). As a result, the measure falls back to returning the variable Shortfall.
Try rewriting your measure
VAR _Metric =
SELECTEDVALUE ( MetricsTable[Metric] )
VAR Shortfall =
[Measure A] - [Measure B]
RETURN
IF (
-- If more than one metric is in context (not at lowest hierarchy level)...
NOT ( HASONEVALUE ( MetricsTable[Metric] ) ),
-- ...then return a fallback measure.
[some other measure],
-- Otherwise, when exactly one metric is selected, decide based on its value:
SWITCH (
_Metric,
"Net", Shortfall, -- If Net, return Shortfall.
"Gross", [some other measure] -- If Gross, return another measure.
)
)
Hi danextian ,
Thank you very much for your help. I tested the measure only looking at the SELECTEDVALUE(MetricsTable[Metric]) and it reads all of it correctly across all layers - Gross/Net when either are selected, so I don't think this is a case of picking up multiple values. Also the Metrics table is stand alone with no relationships so I don't think it's filtering multiple values.
I gave some more context to the measures above in my previous reply if this is any help, if not don't worry and thank you for your help.