topic Re: Dax Calculation needed for matrix visual in DAX Commands and Tips

Dax Calculation needed for matrix visual

Anonymous — Thu, 09 Sep 2021 17:07:53 GMT

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

Re: Dax Calculation needed for matrix visual

Schmidtmayer — Thu, 09 Sep 2021 17:49:59 GMT

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:

Re: Dax Calculation needed for matrix visual

Anonymous — Tue, 14 Sep 2021 10:56:44 GMT

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

Re: Dax Calculation needed for matrix visual

Schmidtmayer — Thu, 16 Sep 2021 14:01:22 GMT

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.

Re: Dax Calculation needed for matrix visual

Anonymous — Fri, 17 Sep 2021 07:55:28 GMT

Thats Fantastic! Thank you very much for your help