Combination of two DAX statements with IF statements for Conditional Formatting rules

[Anonymous]

Hey PBI Community,

I've got a few measures that I'm trying to combine into one so I can have a rule that checks values of 1 & 2, and colour values off that.

I have two that do the same thing, just checking different columns, for reference these are:

Checking highest result based of criteria, marking as 1 if result is greater than or equal to the criteria:

Highest =
VAR ThisResult =
    MAX (Query1[Result])
VAR Criteria=
    MAXX (
        ALLSELECTED (Query1[Result]),
        CALCULATE (
            MAX (Query1[Criteria])
        )
    )
RETURN
    IF (
        ThisResult >= Criteria,
        1,
        0
    )

Checks Range against Criteria, if the Range is greater than or equal to Criteria, then 1 else 0.

Range > Criteria=
VAR Criteria =
    MAXX (
        ALLSELECTED (Query1[Criteria]),
        CALCULATE (
            MAX (Query1[Criteria])
        )
    )
VAR Range =
    MAXX (
        ALLSELECTED (Query1[Range]),
        CALCULATE (
            MAX (Query1[Range])
        )
    )
RETURN
    IF (
        Range >= Criteria,
        1,
        0
    )

These both work with the data that I have, however you can't do a rule based on multiple measures (or so I am led to believe), so I am trying to make both work in the same measure.

I have psuedo that does what I was expecting but it cannot accept multiple IF statements, I have tried ELSE and a comma but I can't figure out if this is correct or not:

ExceedsCheck =
VAR ThisResult =
    MAX (Query1[Result])
VAR Criteria=
    MAXX (
        ALLSELECTED ( Query1[Criteria] ),
        CALCULATE (
            MAX ( Query1[Criteria] )
        )
    )
VAR Range=
    MAXX (
        ALLSELECTED ( Query1[Range] ),
        CALCULATE (
            MAX ( Query1[Range] )
        )
    )
RETURN
    IF (
        Range >= Criteria,
        1,
        0
    )
	IF (
		ThisResult >= Criteria, 
		2, 
		0
	)

EDIT: If both have are 1, then 2?

Thanks!

[jthomson]

Rather than having the false statement of one of the if statements being 0, stick the other if statement in there instead so it's nested into one statement. You'd need to decide which of 1 or 2 you want to show if both criteria are hit (assuming this is possible in your data model) 

[Anonymous]

Thanks @jthomson I've given this a go and understand where you are trying to advise. I don't think an IF statement is the best for this because there are three possible outcomes.

Are you aware of any DAX syntax that allows for three different outputs? I don't think you can nest an IF three times in DAX as it is true or false?

[jthomson · Accepted solution]

You can do this with if - if you want it to prioritise 1 over 2, then you go:

if (Range>=Criteria, 1,if(ThisResult>=Criteria, 2, 0))

If you want it to prioritise 2 over 1, then reverse the order which it looks at first.