Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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/wa...
financial report
SELECTEDVALUE
Switch()
  • tamerj1's avatar
    tamerj1
    3 years ago

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

TomasAndersson's avatar
TomasAndersson
Icon for Solution Sage rankSolution Sage
3 years ago

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.