Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a set of data which looks like the below and I need to determine whether a transaction is Fully Auto, Partially Auto or Manual.
The rule is:
- If a Transaction has User="Auto" for all 3 statuses, then "Fully Auto"
- If a Transaction has User is not "Auto" for all 3 statuses, then "Manual"
- If a Transaction has User = "Auto" for 1 or 2 statuses, then "Partially Auto"
How could I write this DAX measure?
Data
Transaction | Status | User |
Transaction A | Status A | Auto |
Transaction A | Status B | Auto |
Transaction A | Status C | Auto |
Transaction B | Status A | Auto |
Transaction B | Status B | User 1 |
Transaction B | Status C | User 2 |
Transaction C | Status A | User 1 |
Transaction C | Status B | User 2 |
Transaction C | Statucs C | User 3 |
Final Output
Transaction | Outcome |
Transaction A | Fully Auto |
Transaction B | Partially Auto |
Transaction C | Manual |
Outcome =
SWITCH (
TRUE (),
ISEMPTY ( CALCULATETABLE ( Data, Data[User] = "Auto" ) ), "Manual",
COUNTROWS ( VALUES ( 'Data'[User] ) ) = 1, "Fully Auto",
"Partially Auto"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
hi, @nsaray
I am not 100% sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below. It is for creating a measure.
Outcome Measure =
IF (
ISFILTERED ( Data[Transaction] ),
SWITCH (
TRUE (),
COUNTROWS ( VALUES ( Data[Status] ) ) = 3
&& CALCULATE ( COUNTROWS ( Data ), Data[User] = "Auto" ) = 3, "Fully Auto",
COUNTROWS ( VALUES ( Data[Status] ) ) = 3
&& CALCULATE ( COUNTROWS ( Data ), Data[User] = "Auto" ) > 0, "Partially Auto",
"Manual"
)
)
https://www.dropbox.com/s/kryxg2jq7dsql2d/nsaray.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
47 | |
17 | |
12 |