cancel
Showing results for
Did you mean:
Post Prodigy

## SUMX IF measure

Hi ,

With the IF statements below it says SUMX cannot work with type Boolean. Ive checked the formatting and all is correct.

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]>[ParameterEndDate]),1,

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]<[ParameterEndDate]),2,

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]<= [ParameterEndDate]),3,0

Is there a different operator I can use for my measure? New End Date is a column value and the parameter is a measure

1 ACCEPTED SOLUTION
Super User

@lherbert502 , should be like

when you want to display.

measure =
var _max = [ParameterEndDate]
return
Switch( True(), max(RoomUtilisation[New End Date])>_max,1,
max(RoomUtilisation[New End Date])]<[ParameterEndDate],2,
max(RoomUtilisation[New End Date])<= [ParameterEndDate],3,0)

Or, this one is only when want to sum that up

measure =
var _max = [ParameterEndDate]
return
var _max = [ParameterEndDate]
SUMX(RoomUtilisation, Switch( True(), RoomUtilisation[New End Date]>_max,1,
RoomUtilisation[New End Date]<_max,2,
RoomUtilisation[New End Date]<= _max,3,0))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
4 REPLIES 4
Super User

Try something like this...

``````Measure =
SUMX (
RoomUtilisation,
IF (
RoomUtilisation[New End Date] > [ParameterEndDate],
1,
IF (
RoomUtilisation[New End Date] < [ParameterEndDate],
2,
IF ( RoomUtilisation[New End Date] = [ParameterEndDate], 3, 0 )
)
)
)``````

OR

``````Measure =
SUMX (
RoomUtilisation,
SWITCH (
TRUE (),
RoomUtilisation[New End Date] > [ParameterEndDate], 1,
RoomUtilisation[New End Date] < [ParameterEndDate], 2,
RoomUtilisation[New End Date] = [ParameterEndDate], 3,
0
)
)``````

Thank you, good sir. I've been racking my brain for days trying to figure this out. Putting the switch inside the sumx made all the difference.

Super User

@lherbert502 , should be like

when you want to display.

measure =
var _max = [ParameterEndDate]
return
Switch( True(), max(RoomUtilisation[New End Date])>_max,1,
max(RoomUtilisation[New End Date])]<[ParameterEndDate],2,
max(RoomUtilisation[New End Date])<= [ParameterEndDate],3,0)

Or, this one is only when want to sum that up

measure =
var _max = [ParameterEndDate]
return
var _max = [ParameterEndDate]
SUMX(RoomUtilisation, Switch( True(), RoomUtilisation[New End Date]>_max,1,
RoomUtilisation[New End Date]<_max,2,
RoomUtilisation[New End Date]<= _max,3,0))

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Post Prodigy

Perfect thanks @amitchandak

Announcements

#### Launching new user group features

Learn how to create your own user groups today!