Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a matrix table that pulls together and aggregates various columns from different tables and in the same matrix table a measure that returns a yes/no for each row within the table.
What I would like to do next is create a new measure in a card above that does a count of the measure in the matrix table for all the "Yes" outcomes.
Have not been able to crack it or find anything online to help, so hoping someone on here can help? 🙂
Thanks,
Ben
Solved! Go to Solution.
Thanks for the additional info. Still not totally clear on your scenerio but please try this expression
Yes Count =
COUNTROWS (
FILTER (
SUMMARIZE ( Core, Core[Name], Core[Type], Core[Size] ),
[Variance Status ‘Yes/No’] = "Yes"
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try a measure expression like this for your card, where column1 and column2, etc. are the columns used in your matrix visual.
Yes Count =
COUNTROWS (
FILTER (
SUMMARIZE ( Table, Table[Column1], Table[Column2] ),
[Your YesNo Measure] = "Yes"
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for the response @mahoneypat but I couldn’t get that to work, I just get a BLANK return in the card. I think I might not be filling in the DAX correctly so I've provided more detail on my matrix table below.
Rows:
Table Core: Name
Table Core: Type
Table Core: Size
Values
Table 2000: Units
Table 2001: Units
Table 2000: Cost
Table 2001: Cost
Measure 1: Variance
Measure 2: Variance Status ‘Yes/No’
So how would I structure that DAX to count measure 2 for the number of Yes?
Thanks for the additional info. Still not totally clear on your scenerio but please try this expression
Yes Count =
COUNTROWS (
FILTER (
SUMMARIZE ( Core, Core[Name], Core[Type], Core[Size] ),
[Variance Status ‘Yes/No’] = "Yes"
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks again , appreciate your patience 😊 however still can't get this working. I've tried to add more information below to help understand what I've got.
I’m working with 3 tables;
The Main which I'm using 3 fields; Name, Type and Size and then the other two tables link to this in a 1-2-1 relationship based on a unique ref number. These other two tables are for previous year and current year and hold quantities and costs.
Finally I have two measures, the first being a variance of the cost for year and year in a percentage format and then the second measure is checking which are out of tolerance by yes/no and it’s a count of this second measure that I want to get and add into a card.
Then added into my matrix table, aggregated as shown below.
Matrix Row | Matrix Row | Matrix Row | Matrix Value | Matrix Value | Matrix Value | Matrix Value | Matrix Value | Matrix Value |
Name | Type | Size | 2020 Units | 2021 Units | 2020 AVG Cost | 2021 AVG Cost | Variance | Tolerance Out |
Fitzgernal inc | commerical | large | 100 | 150 | 98.33 | 105 | 7% | Yes |
I have tried the expression you have suggested but it just returns (BLANK)
Yay sucess! I'm not sure exactly what I did differently but it's working now. Thanks for the help @mahoneypat
Ben
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |