cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## DAX Help - Determine value using occurrence on sub-set of data

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
2 REPLIES 2
Community Champion
``````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!
Super User

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

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.

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.

Announcements