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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.