Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Stubby
Helper I
Helper I

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.

Dax Question.jpg

 

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
Schmidtmayer
Helper II
Helper II

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

Schmidtmayer_0-1631209070213.png

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:

Schmidtmayer_1-1631209399490.png

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_2-1631209740590.png

 



View solution in original post

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.

View solution in original post

4 REPLIES 4
Stubby
Helper I
Helper I

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

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.

Thats Fantastic! Thank you very much for your help

Schmidtmayer
Helper II
Helper II

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

Schmidtmayer_0-1631209070213.png

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:

Schmidtmayer_1-1631209399490.png

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_2-1631209740590.png

 



Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.