cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## How to count the frequency output of a measure by date

I have a measure which evaluates whether a threshold is surpassed or not, and by how many units.

``````Alert = IF(
[Volume_C] > [YMax], "Y - Above 2 Standard Deviation",
IF(
[Volume_C] < [YMin], "Y - Below 1 Standard Deviation", "No Alert"
))``````

I am trying to sum up the number of rows I get on this date that say "Y - ..." whether it is above or below a certain standard deviation - but I end up counting the entire rows of the data set ~15,000 rather than just 1

``````Alert Sum =
COUNTROWS(
))``````

EDIT:

How can I count the rows from the measure 'Alert' conditionally as "Y - above stdev" or "Y - Below stdev" for the most previous day and push it to a card? I am taking the standard deviation of past 60 days for a list of categories, if a certain volume of each category goes over two standard deviations, the [Alert] measure will indicate "Y - above stdev'. If below stdev, [Alert] will indicate 'Y - below stdev'. If between the two stdev, [Alert] = 'No Alert'. I want to take a sum of the category counts for whenever [Alert] measure indicats some form of 'Y' and push the total number of [Alert]='Y' to a card for the previous day.

In this case, there is only 1 category indicated as 'Y - Below 1 stdev', and the card should say 1. But instead takes a sum of the entire number of alerts that say yes for the past 60 days.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

@Langutang - OK, I think something like:

``````Measure =
VAR __Date = MAXX(ALL('Table'),[Date])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[Date]=__Date),
[Call Reason],
)
RETURN
COUNTROWS(FILTER(__Table,FIND("Y - ",[Measure],,0)>0)))``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

9 REPLIES 9
Highlighted
Super User IV

@Langutang ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Proud to be a Super User!

Highlighted
Solution Sage
"I am trying to sum up the number of rows I get on this date that say "Y - ..."

Well, we don't know what is the granularity you want to sum over.... so currently nobody will be able to help you. You have to show what it is that you want to calculate your sum over.
Highlighted
Super User IV

@Langutang This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

Highlighted
Super User IV

@Langutang What else is in that table besides what you have shown? Date I assume, anything else? Like an ID or ? Is each row in that table a different item on the same day? Sorry, still unclear.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

@Greg_Deckler, thank you for the help - I can try to be clearer

So I have a 30 std dev calculation for each category - in the call reason. If the Value of each call reason category surpasses a stdev, above or below, ['Alert'] = 'Y- Below 1 Stdev' or ['Alert'] = 'Y-Above 1 Stdev'. If the value of a category in the column DOES NOT surpass a std dev on a particular day, the ['Alert'] = 'No Alert'.

I want to go through the most recent day (Now 9/14, earlier post is 9/13) and count the number of times there is ['Alert'] = 'Y...' Whether it is below or above stdev, and push that value to a card.

In this case the card would say '28' since every category triggered [Alert] = 'Y..'. Yesterday the card would have said '1' (from original post)

My problem is how can I get this count sum from the most recent day?

Highlighted
Super User IV

@Langutang - OK, I think something like:

``````Measure =
VAR __Date = MAXX(ALL('Table'),[Date])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[Date]=__Date),
[Call Reason],
)
RETURN
COUNTROWS(FILTER(__Table,FIND("Y - ",[Measure],,0)>0)))``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

This worked beautifully! Thank you!

Highlighted
Super User IV

@Langutang Awesome! Glad to hear it! The right information goes a long way!! 🙂

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors