Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
chris_94
New Member

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

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.

chris_94_4-1672411008224.png

 

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

chris_94_1-1672410422534.png

 

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

chris_94_0-1672410359082.png

 

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

chris_94_3-1672410627519.png

 

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@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.

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

@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.

@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?

@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
Solution Sage
Solution Sage

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. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors