Dax Calculation needed for matrix visual

[Stubby]

Hello all.

I have a fact table that looks at training courses data. For a member of staff to be considered Compliant they must have a completion status in any one of three courses (which I have called Part1Pass)  and a completion status in any one of 15 other courses (which I have called Part2Pass).  I have created two calculated columns which returns "yes" if the condition is true and "no" if not. The problem I have is the two conditions i want to evaluate are on different rows for the same member of staff. Using Dax Studio I have created the below to demonstrate what I am talking about.

The DAX I want to create would check for both values of yes and if so show the members of staff in a a visual.

Is this possible?

Any help much appreciated

[Schmidtmayer · Accepted solution]

I prefer to work with numbers, so, this might look a bit too complicated. Just took the following sample data:

From your logic, a would be staff, b,c,d wouldn't.

First: Transform these yes and no into numbers no = 0 and yes = 1, using calculated columns:

test1passint = IF(test1pass = "yes", 1, 0)

test2passint = IF(test2pass = "yes", 1, 0)

This gives this result:

Now you can transform text into sums, define the following measure:

isStaff = IF(SUMX(table, test1passint) = 1 && SUMX(table, test2passint) = 1, 1, 0)

Now placing all persons in a table and putting isStaff as a filter with 1 as its value, returns just a:

[Schmidtmayer · Accepted solution]

I prefer to work with numbers, so, this might look a bit too complicated. Just took the following sample data:

From your logic, a would be staff, b,c,d wouldn't.

First: Transform these yes and no into numbers no = 0 and yes = 1, using calculated columns:

test1passint = IF(test1pass = "yes", 1, 0)

test2passint = IF(test2pass = "yes", 1, 0)

This gives this result:

Now you can transform text into sums, define the following measure:

isStaff = IF(SUMX(table, test1passint) = 1 && SUMX(table, test2passint) = 1, 1, 0)

Now placing all persons in a table and putting isStaff as a filter with 1 as its value, returns just a:

[Stubby]

That worked a treat, Thank you!

Could you help with another step! Based on the above, if i wanted to display in a card a count of compliant staff what measure would I use.

Many thanks in advance

[Schmidtmayer · Accepted solution]

Define the following measure (I use __ to indicate I use variables in measures)

numbersstaff =

VAR __stafftable = SUMMARIZE(
          table,
           person,
           "member of staff",
            isStaff (the measure from above)
)

RETURN SUMX(__stafftable, __stafftable[member of staff])

This is like pregrouping the data for further calculations. Really helpful in case you wanna work just with raw data.

[Stubby]

Thats Fantastic! Thank you very much for your help