Measure for IF not working

[misstoffeepenny]

Hi all, I'm trying to write a DAX for an IF function where there are multiple criteria and true answers but it keeps getting stuck and generating on 1 answer for all outcomes any help correcting it would be appreciated.
NB all my tables are linked in data relationships.
Below is what I have so far:
=IF (
MAX ( SiteInspections[count major] ) > 1, 0,
IF (
MAX ( SiteInspections[count major] ) <= 1, 5,
IF (
MAX ( SiteInspections[count minor] ) > 5, 5,
IF (
MAX ( SiteInspections[count minor] ) <= 5, 10,
IF (
MAX ( SiteInspections[count minor] ) < 1, 15,
IF (
MAX ( SiteInspections[count OFI] ) > 5, 15,
IF (
MAX ( SiteInspections[count OFI] ) <= 5, 20, BLANK()
)
)
)
)
)
)
)
this gives all answers as 5... please help

 

[RoryA]

IF will stop with the first test that is true, so you need to order the tests accordingly. Currently if this is true:

MAX( SiteInspections[count minor] ) > 5

it will never consider any of the criteria after that. It's not clear from your questions what priority any of them have over the others or what combinations are needed (though obviously testing for the MAX count minor being <1 after testing for it being <=5 is never going to return the second option)

 

[misstoffeepenny]

IF will stop with the first test that is true, so you need to order the tests accordingly. Currently if this is true:

MAX( SiteInspections[count minor] ) > 5

it will never consider any of the criteria after that. It's not clear from your questions what priority any of them have over the others or what combinations are needed (though obviously testing for the MAX count minor being <1 after testing for it being <=5 is never going to return the second option)

RoryA, thank you for your reply, I am still learning the DAX/ power pivot abilities

1. this order is the most important as major is a 'red flag', minor is amber, ofi is green etc.
2. with this formula even cells which have no data to look up are giving me a value of 5.
3. I've removed that line from the formula & refreshed it & again all still getting 5.

 

[RoryA]

That would suggest that count major is setting it - anything <=1 (including 0) would cause that.

 

[misstoffeepenny]

That would suggest that count major is setting it - anything <=1 (including 0) would cause that.

thank you for your help, this made me realise I needed to write in a >0 statement as well. so I managed to fix it with:

=IF(
MAX(SiteInspections[count major]) > 1, 0,
IF(
MAX(SiteInspections[count major]) = 1, 5,
IF(
MAX(SiteInspections[count minor]) > 5, 5,
IF(
AND(MAX(SiteInspections[count minor]) > 0, MAX(SiteInspections[count minor]) <= 5), 10,
IF(
MAX(SiteInspections[count OFI]) > 5, 15,
IF(
AND(MAX(SiteInspections[count OFI]) > 0, MAX(SiteInspections[count OFI]) <= 5), 20,
BLANK()
)
)
)
)
)
)

 

[RoryA]

You shouldn't really need the ANDs since the criteria immediately before each one have already ruled out anything over 5:

=IF(
MAX(SiteInspections[count major]) > 1, 0,
IF(
MAX(SiteInspections[count major]) = 1, 5,
IF(
MAX(SiteInspections[count minor]) > 5, 5,
IF(
MAX(SiteInspections[count minor]) > 0, 10,
IF(
MAX(SiteInspections[count OFI]) > 5, 15,
IF(
MAX(SiteInspections[count OFI]) > 0, 20,
BLANK()
)
)
)
)
)
)