The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Greetings Community!
I need assistance with a cumulative measure that sums the number of times a specific text value is returned from several other measures. This is what my Matrix looks like:
Column "Project Number" is the Project Number.
Columns Capital Above, Lapsed Ship Date, Missing Ship Date, NPV Below, NPV Over, and Volume Over are all measures that return "Ok" if that aspect of the project does not violate a preestablished threshold and "Review" if it does. For example: if a project has a positive capital investment amount, the measure returns "Review" and vice versa. If a project's ship date has passed, the measure returns "Review" and vice versa. So on and so forth.
As you can see, we have multiple projects that have violated multiple thresholds and the measures are flagging them for review. For example, Project 1069672 has been flagged for violating the capital amount, ship date has passed, and sales volume thresholds.
My question is, how can I create a measure to count the number of times the other measures produce a "Review" result. Put another way, Project 1069672 has three things that need to be reviewed. The result I am looking for is as follows:
Project Number | Flag Count |
1069672 | 3 |
1070717 | 2 |
1072947 | 1 |
etc... | etc... |
Solved! Go to Solution.
Hi there @Anonymous
Here is one way you could write this:
Flag count =
VAR MeasureValues = {
[Capital Above],
[Lapsed Ship Date],
[Missing Ship Date],
[NPV Below],
[NPV Over],
[Volume Over]
}
RETURN
SUMX ( MeasureValues, INT ( [Value] = "Review" ) )
This assembles the measure values into a single table, then the count of "Review" values is found by summing over this table.
INT ( [Value] = "Review" ) converts True to 1 and False to 0.
You could also consider changing the original measures so that they return 0/1 values, with a format string like "Review";"ERROR";"Ok"
This would allow you to sum [Value] within SUMX, but still have the measures appear the same in visuals.
Regards,
Owen
That's good, you're welcome 🙂
In order to sum across projects, you essentially need to wrap the earlier measure in a SUMX. You could do this by modifying the original measure (you could also put it in a second measure referencing the first):
Flag count =
SUMX (
VALUES ( YourTable[Project Number] ),
VAR MeasureValues = {
[Capital Above],
[Lapsed Ship Date],
[Missing Ship Date],
[NPV Below],
[NPV Over],
[Volume Over]
}
RETURN
SUMX ( MeasureValues, INT ( [Value] = "Review" ) )
)
This measure should work either when grouping by individual projects or by Project Owners.
Does this work as expected?
Hi @OwenAuger
Excellent this works perfectly!
Thank you for your assitance and taking the time to read through my post.
-GC
Hi @OwenAuger
This works perfectly, thank you as always!
Another question for you though, based on what you said about changing the original measures so that they return 0/1 values. This brings up another idea:
Each project has a project owner, and a project owner might be responsible for several projects. Let's say Project Owner A is responsible for 8 different projects. Of those 8 projects, their total flag count is 12. How would you write that DAX formula so I could show that total number on a Card visual?
That's good, you're welcome 🙂
In order to sum across projects, you essentially need to wrap the earlier measure in a SUMX. You could do this by modifying the original measure (you could also put it in a second measure referencing the first):
Flag count =
SUMX (
VALUES ( YourTable[Project Number] ),
VAR MeasureValues = {
[Capital Above],
[Lapsed Ship Date],
[Missing Ship Date],
[NPV Below],
[NPV Over],
[Volume Over]
}
RETURN
SUMX ( MeasureValues, INT ( [Value] = "Review" ) )
)
This measure should work either when grouping by individual projects or by Project Owners.
Does this work as expected?
Hi there @Anonymous
Here is one way you could write this:
Flag count =
VAR MeasureValues = {
[Capital Above],
[Lapsed Ship Date],
[Missing Ship Date],
[NPV Below],
[NPV Over],
[Volume Over]
}
RETURN
SUMX ( MeasureValues, INT ( [Value] = "Review" ) )
This assembles the measure values into a single table, then the count of "Review" values is found by summing over this table.
INT ( [Value] = "Review" ) converts True to 1 and False to 0.
You could also consider changing the original measures so that they return 0/1 values, with a format string like "Review";"ERROR";"Ok"
This would allow you to sum [Value] within SUMX, but still have the measures appear the same in visuals.
Regards,
Owen
Good day @OwenAuger ,
I hope you are doing well. I have a similar (simpiler?) issue but I cant figure out how to resolve.
I am working on an OLAP cube so I cannot create any columns in the model, only measures.
I am trying to build a measure that counts the number of times the Feed Flag = "High" and another one to show the number of times the Feed Flag = "Low" as per the below. All of the highlighted columns in the visual below are measures I have created based on the "feed kg Act" column. I have built a second flag using decimals to flag "high" and "Low", not sure if this is better to use?
I want to add cards and other visuals to summarize this table based on this information.
Warmest regards,
Katie
I'm fine thanks, and I hope you're well too.
You can use a pattern like this to count occurrences of a certain measure value:
Feed Flag High Count =
COUNTROWS (
FILTER (
SUMMARIZE (
YourTable,
-- The columns listed below are the columns to group by
-- when evaluating the measure.
YourTable[Unit],
YourTable[Date]
),
[Fee Flag] = "High"
)
)
Notes:
Does this work for you?
Kind regards,
Owen
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
18 | |
18 | |
16 | |
14 | |
12 |
User | Count |
---|---|
36 | |
35 | |
20 | |
18 | |
18 |