How to use an IF and IN function in DAX

[Anonymous]

Hi

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'm hoping to achieve is for each distinct instance in [TMS LINK] (eg. 101173549) to either be "Compliant" or "Not Compliant" and this is determined by the column [Load Comp].
There are 10 distinct values where 3 are "Compliant" and 7 are "Not Compliant"
A [TMS LINK] value is classified as "Compliant" if all rows in [Load Comp] for that particular value is "Compliant"
(eg. 101173549 is "Not Compliant", where as 101173150 is "Compliant")

I've been trying to search for an answer here but to no avail and I'm new to using powerBI so it's been quite challenging to solve. Any help would be much appreciated.

[parry2k · Accepted solution]

@Anonymous you can easily do it like this, add the following measure

New Measure = 
VAR __values = CALCULATETABLE ( VALUES ( Table[Load Comp] ), ALLEXCEPT ( Table, Table[TMS Link] ) )
RETURN
IF ( 
"Not Compliant" IN __values, "Not Compliant",
"Compliant"
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous whatever other table is add following measures:

Total Loads = COUNTROWS ( OtherTable ) 

or 

Total Load = DISTINCTCOUNT ( OtherTable[Column] )

Total Compliant = COUNTX ( VALUES ( Table[TMS Link] ), IF ( [New Measure] = "Compliant", 1 ) )

Compliant % = DIVIDE ( [Total Load], [Total Compliant] )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Anonymous you can easily do it like this, add the following measure

New Measure = 
VAR __values = CALCULATETABLE ( VALUES ( Table[Load Comp] ), ALLEXCEPT ( Table, Table[TMS Link] ) )
RETURN
IF ( 
"Not Compliant" IN __values, "Not Compliant",
"Compliant"
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thanks heaps for your help!

[Anonymous]

Cheers for the formula it's working perfectly! If I may throw in an extra layer I'd want to turn this into something as below where it calculates everything in one table. The table visual is sourced from two different files however, if I use the above example then I would expect to have 30 Total Loads (not the same source table as example provided above) and 3 Compliant Loads (as per the example above) then I would like to have Compliance % where it would be Total Loads / Compliant Loads.

The Line Graph Visual would then look at the last 6 weeks where the bars are Total Loads and the trendline would be Compliant Loads.

A bit complex but I hope I explained that pretty clearly.