How to use an IF and IN function in DAX

[Syndicate_Admin]

Hello

I'm trying to create a measure that basically says; If [Load Comp] - "Not Compliant" IN [TMS LINK] then "Not Compliant" Else "Compliant"

What I hope to achieve is that for each different instance in [TMS LINK] (for example, 101173549) it is "Compatible" or "Non-compliant" and this is determined by the [Load Comp] column.
There are 10 different values where 3 are "compatible" and 7 are "Non-compliant"
A value [TMS LINK] is classified as "Compliant" if all rows in [Load Comp] for that particular value are "Compliant"
(for example, 101173549 is "Not supported", where as 101173150 is "compliant")

I've been trying to find an answer here, but to no ava pity and I'm new to using powerBI so it's been pretty hard to solve. Any help would be greatly appreciated.

[v-yuaj-msft · Accepted solution]

Hi @Syndicate_Admin ,

Based on your description, you can create a measure as follows.

My test table:

Measure:

Measure = 

var x1=IF(MAX('compatibale'[Load Comp])="Non-compliant",1,0)

var x2=SUMX(ALLEXCEPT(compatibale,compatibale[TMS LINK]),x1)

return

IF(x2>0,"Non-compliant","Compliant")

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Syndicate_Admin , if you want look at mutiple lines for this. This as a new column can work

new column =
var _count = countx(filter(Table,[TMS LINK] =earlier([TMS LINK]) && [Load Comp] = "Not Compliant"),[TMS LINK])
return
if(isblank(_count) ,"Compliant", "Not Compliant")