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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 I
Helper I

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 I
Helper I

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_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.