cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax Calculation needed for matrix visual

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

2 ACCEPTED SOLUTIONS
Helper I

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:

Helper I

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

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.

4 REPLIES 4
Frequent Visitor

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.

Helper I

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

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.

Frequent Visitor

Thats Fantastic! Thank you very much for your help

Helper I

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:

Announcements