cancel
Showing results 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

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.

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

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:

Helper I

Define the following measure (I use __ to indicate I use variables in measures)

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 4
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.

Helper I

Define the following measure (I use __ to indicate I use variables in measures)

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.

Helper I

Thats Fantastic! Thank you very much for your help

Helper I

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:

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors