cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Inesgc
New Member

DAX: Sum of a Switch Statement

Hi, 

I am very new to Power BI and I am trying to add a new column that shows me the variation between two other columns, but I am having problems because my values are calculated based on a switch statement. 

 

I have a matrix where in the rows there is the description, in the values the switch statement, and in the column I have a "Scenario" that can have 2 values ("Real" and "Contractual"). 

 

The measure for the switch is named "Measure Year Appointment" 

 

And the look of my variation measure is this: 

Variation :=

VAR R = CALCULATE(SUM([Measure Year Appointment]),FILTER('Scenario','Scenario'[Scenario]="Real"))
VAR C = CALCULATE(SUM([Measure Year Appointment]),FILTER('Scenario','Scenario'[Scenario]="Contractual"))
return
DIVIDE(R - C, C)

 

And I am having this error message: "the sum function only accepts a column reference as the argument number 1"

 

Thanks in advance!

 

 

 

3 REPLIES 3
Mariusz
Super User II
Super User II

Hi @Inesgc

You can use SUMX() or just measure without the SUM() around if it does already contain SUM(), it's hard to tell if we can not see the

[Measure Year Appointment] syntax.
 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.


Hi, 

 

Sorry, my bad, here it is (it is a really big one so i will just put (...) because the process is smiliar to what is shown in the first 2 values: 

 

Measure Year Appointment = SWITCH(TRUE(),FIRSTNONBLANK('Measure Dashboard'[Appointment Description], 1) = "Total of Medical Appointments", [Total of Medical Appointments], FIRSTNONBLANK('Measure Dashboard'[Appointment Description],1) = "First Appointments", [First Appointments],(...),BLANK(),BLANK())

Hi @Inesgc 

 

You should be able to use SUMX() instead of SUM() in your CALCULATE 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors