Combined SWITCH and SELECTEDVALUE function not working for sum of multiple measures

[chris_94]

Hi all,

I'm trying to set up a DAX-based table visual for a financial statement - based on financial transactions.

My current set up is now based on a YouTube guide (https://www.youtube.com/watch?v=J4317R5BvsA&t=539s).

The major difference between my approach and the one in the YouTube guide is that I'm working on financial transactions rather than aggrecated categories.

So far so good, but I'm running into trouble when trying to get values from a measure that sums other measures e.g. a gross margin, which shows a blank.

My measure for retrieving and lining up the correct line item values looks like this:

The measures that do work, are all written like this:

The measures that don't work, are all written like this:

I'm not a super user of Power BI at all, but I suspect that the "sum measures" shows a blank due to the fact that my "sum measures" contain multiple measures. I tried replacing the DAX code with a simple hard typed numbers (e.g. 1000-500-250) formula, which does show up in my table.

Any ideas for a workaround or a totally different approach is much appreciated.

Happy holidays! 😎

Best regards,
Chris

[tamerj1 · Accepted solution]

@chris_94 
Yes that is a possibility. The engine optimizer might return a blank value and ignores the evaluation of other measures if they came along with a measure that evaluates to a blank.

However, it is worth asking whether you have checked if PowerBi has automatically created a relationship between 'Report Structure'[Line_item_ID] and 'Account mapping'[GLA_Line_item_ID] without you noticing that. That is also a possibility.

[chris_94]

@tamerj1 you were absolutely right. Deleting the relation fixed it instantly! My boss will be very pleased.

Thanks a lot.

Actually I created the relationship myself in an earlier rendition.

Could you please explain why the relationship caused the error?

[tamerj1]

@chris_94 
It is similar to slicing by 'Account mapping'[GLA_Line_item_ID] then using a SWITCH statement on top of it. For sure you'll have EMPTY filter intersections. For example the measure that filters 'Account mapping'[GLA_Line_item_ID] = "Revenue" will have a blank value when the line item is not "Revenue" and so on.

[TomasAndersson]

Hi!
Yeah I'm actually not sure why it doesn't work. I'm also thinking it might be that there are several measures that "references" several Line_item_IDs which causes SELECTEDVALUE() to returning blank in those cases. Do the measures show blank when they're "by themselves", i.e. not in that SWITCH()/SELECTED() but for example just as a KPI card as well?

One potential workaround (but I'm not sure) could be to create these measures as variables before the SWITCH() statement, as variables are calculated before calling any function after return.

So for example: 

Reporting Value = 
VAR __CM1 = 
([Revenue] + [CoGS] + [Prod. labor costs]) / 1000
return 
SWITCH( 
[...]
    "Contribution margin I", __CM1,
[...]
)

 Maybe worth a try.