cancel
Showing results for
Did you mean:

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.

Anonymous
Not applicable

## Count The Number of Times a Measure Returns a Specific Value

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

2 ACCEPTED SOLUTIONS
Super User

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

Owen Auger
Blog
Super User

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?

Owen Auger
Blog
7 REPLIES 7
Anonymous
Not applicable

Excellent this works perfectly!

Thank you for your assitance and taking the time to read through my post.

-GC

Anonymous
Not applicable

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?

Super User

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?

Owen Auger
Blog
Super User

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

Owen Auger
Blog
Helper I

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

Super User

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:

• Within SUMMARIZE, the first YourTable should be replaced by the relevant fact table name (presumably the table containing Feed Kg Act values).
• YourTable[Unit]and YourTable[Date]should be changed to correct column references.
• The group-by columns within SUMMARIZE in this example are YourTable[Unit] and YourTable[Date], which I assumed was sufficient to give the same granularity as the table visual in your screenshot. You can change these to a list of whatever columns are required to define the granularity for calculating the measure.
• [Fee Flag] = "High" can be replaced with any other condition you want to count involving a measure.

Does this work for you?

Kind regards,

Owen

Owen Auger
Blog
Helper I

This worked 100% perfectly. Thank you so much!!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors