Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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:

gordo_c_123_0-1647123982688.png

 

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 NumberFlag Count
10696723
10707172
10729471
etc...etc...

 

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi @OwenAuger 

 

Excellent this works perfectly!

 

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

 

-GC

Anonymous
Not applicable

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
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
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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. 

KatieFarrand12_1-1695026714098.png

 

 

Warmest regards,

Katie

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors