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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
More_BI
Frequent Visitor

Switch function breaking my chart

Hey guys,

 

I use the switch function in various measures to carefully toggle how the KPI will be implemented. For example, I have a column called "Rollup Type" which is tied to the list of KPIs. It determines how the KPI should be aggregated -- end of day metric or just a plain sum. 

 

So in many of my calculations, I have an expression similar to the following,

 

a_test = SWITCH(VALUES('KPI List'[Rollup Type]),
        "SUM", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"), 
"EOB", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"))

 

*Yes, I know both versions are identical... please disregard that part for now

 

So I will add this measure to my chart and it will break the chart. My columns are KPI, Country, Rollup Type --this works just fine. When I add the measure above, it breaks. It is worth noting, there are no duplicate rows being created, there is only one rollup type per KPI. 

 

The error I get, "a table of multiple values was supplied where a single value was expected"

 

I'm not sure how this is possible when I was only getting one value for the rollup type. This chart does NOT break when I add a measure containing only part of the above measure,

 CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual")

 

 

Can someone shed some light on what is happening here?

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @More_BI,

 

Try to change your measure to something like this:

 

a_test = SWITCH(MAX('KPI List'[Rollup Type])
"SUM", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"), "EOB", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"))

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @More_BI,

 

Try to change your measure to something like this:

 

a_test = SWITCH(MAX('KPI List'[Rollup Type])
"SUM", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"), "EOB", CALCULATE([ValueAtCurrency], 'KPI Data'[Version] = "Actual"))

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Yup, that worked. But why was my original measure causing multiple values? I want to better understand how this function works. Thanks!

Hi @More_BI,

 

The VALUES formula returns a list of values( see link with explanation) so you when you use it to return a single value to your visuals it will give you the error you have, since in the DAX measure the context of the measures are import for the final results, usually values is used in the filters or to sum other values.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Interesting... that makes sense. So the values function overrrides the switch function, is that correct? 

 

If I understand the switch function, it should return one value.

 

How is the max function working here? Does it only get one result back from the switch and therefore will always be the right value?

Hi @More_BI,

 

As part of the argument on the SWITCH since the VALUES return more than one value the SWITCH will have an error, when you use MAX or MIN you are returning only one value so complying with the arguments needed in the SWITCH.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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