cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iampaoloc
Frequent Visitor

How to use an IF and IN function in DAX

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")

Mobility.JPG

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.

2 ACCEPTED SOLUTIONS
parry2k
Super User III
Super User III

@iampaoloc 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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

parry2k
Super User III
Super User III

@iampaoloc 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
parry2k
Super User III
Super User III

@iampaoloc 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

parry2k
Super User III
Super User III

@iampaoloc 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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Thanks heaps for your help!

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.
Capture.JPG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.