topic Re: Count The Number of Times a Measure Returns a Specific Value in DAX Commands and Tips

Count The Number of Times a Measure Returns a Specific Value

Anonymous — Sat, 12 Mar 2022 22:44:40 GMT

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

Re: Count The Number of Times a Measure Returns a Specific Value

OwenAuger — Sun, 13 Mar 2022 07:09:17 GMT

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

Re: Count The Number of Times a Measure Returns a Specific Value

Anonymous — Sun, 13 Mar 2022 18:56:44 GMT

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?

Re: Count The Number of Times a Measure Returns a Specific Value

OwenAuger — Sun, 13 Mar 2022 20:45:36 GMT

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?

Re: Count The Number of Times a Measure Returns a Specific Value

Anonymous — Mon, 14 Mar 2022 22:09:25 GMT

Hi OwenAuger

Excellent this works perfectly!

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

-GC

Re: Count The Number of Times a Measure Returns a Specific Value

Katie-Farrand12 — Mon, 18 Sep 2023 08:48:27 GMT

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

Re: Count The Number of Times a Measure Returns a Specific Value

OwenAuger — Mon, 18 Sep 2023 11:16:28 GMT

Hi Katie-Farrand12

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

Re: Count The Number of Times a Measure Returns a Specific Value

Katie-Farrand12 — Mon, 18 Sep 2023 12:54:11 GMT

Hi OwenAuger

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