Forum Discussion
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
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: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.
4 Replies
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: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.