Help with using SWITCH() syntax

[naizam]

Hello experts,

I am trying to create a calculated measure with a condition. The condition needs to look at a dimension and if its a certain data element, do a sum of another measure. Below is what i have if it makes sense

SWITCH(SELECTEDVALUE('public v2_rcnt_6_wks'[Metric]),"1-16M",CALCULATE(SUM('public v2_rcnt_6_wks'[mtrc_num_delta_row_tot])))

So here [Metric] is the dimension and if it is "1-16M", it should add the numbers in column [mtrc_num_delta_row_tot].

The above formula is not working as expected, seems like the syntax is wrong. 

Any help is appreciated.

Thanks

Naizam

[Greg_Deckler]

OK, define "wrong". Is it an error, some unexpected value? That is a pretty dirt simple SWITCH statement, not a ton to go wrong there, here it is cleaned up a bit:

SWITCH(
  SELECTEDVALUE('public v2_rcnt_6_wks'[Metric]),
  "1-16M",CALCULATE(SUM('public v2_rcnt_6_wks'[mtrc_num_delta_row_tot]))
)

[naizam]

Thank you for the quick response. The results are blank. Nothing showing up even though there is data in the table.

Tried the cleaned up version you provided and still the same.

Thanks,

Naizam

[amitchandak]

Please try

 //If based in slicer 
 var _max = maxx('public v2_rcnt_6_wks',allselected('public v2_rcnt_6_wks'[Metric]))
 return
 SWITCH(_max,"1-16M",CALCULATE(SUM('public v2_rcnt_6_wks'[mtrc_num_delta_row_tot])))
 
 
 //if not a slicer
SWITCH(firstnonblank('public v2_rcnt_6_wks'[Metric],blank()),"1-16M",CALCULATE(SUM('public v2_rcnt_6_wks'[mtrc_num_delta_row_tot]))) 

[naizam]

Hi Amit,

Unfortunately this is also not working. I am wondering if using IF ELSE would make any difference.

Thanks,

Naizam

[naizam]

Below is what i see without using Metric field in the report layout even though there are many other records with data.

And this how it looks after adding Metric field to the layout.

[Greg_Deckler]

OK, any possibility you can share some sample source data that replicates the problem for this or the PBIX? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[naizam]

Hi @Greg_Deckler ,

Thank you for looking into this. As you mentioned, it is a simple math and i am not sure where it is going wrong. 

Below is the link to the pbix.

https://drive.google.com/open?id=12porBnt7aoZxg38MkXxDudMiRCqacOqp

Thanks,

Naizam

[naizam]

Anyone know what could be wrong here? Appreciate any help you can provide.

Thanks,

Naizam

[HotChilli]

Looking at the pbix, I think you've already proved what's going on with the 2 different tables you have.

The one on the left just has state and the three measures. It only shows data for Marshall Islands and Micronesia. These are the only states where the table can evaluate only one value for Metric.  Any other State has more than one record with different Metric.  So SelectedValue returns blank.

I tested this by creating a measure

testMeasure = SELECTEDVALUE(Sheet1[Metric])

and putting it on a card.  In the left hand table I put 'Show items with no data' on the state field.  Click on any state apart from the two mentioned, the card always shows blank.

You have proved with the table on the right that the selectedvalue can return a value because you have added another field to the table.  That gives enough detail.

[naizam]

Thank you for looking into this. Let's say for example, i want to display the aggregate on a card. Right now, it is not displaying anything unless i have a slicer (or a left hand table like you had) to select each state. Any best way to make it aggregate without state or metric field in the layout?

[naizam · Accepted solution]

Thank you everyone. I used something like below and it works

CALCULATE(SUM('public v2_rcnt_6_wks'[mtrc_num_delta_row_tot]),FILTER('public v2_rcnt_6_wks', [Metric] = "1-16M"))