cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lherbert502
Post Prodigy
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
amitchandak
Super User
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))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

4 REPLIES 4
sreenathv
Super User
Super User

Your syntax is wrong.

 

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. 

amitchandak
Super User
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))

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

Perfect thanks @amitchandak 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.