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(
FILTER(ALLEXCEPT('Verizon Call Reason','Verizon Call Reason'[call_reason_derived] ), CALCULATE([Alert] = "No Alert", DATEADD('Verizon Call Reason'[session_date],-1,DAY))
))``````

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.

This card is [Alert Sum]

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)))``````

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

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.

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Frequent Visitor

I made an edit, please let me know if this helps

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.

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

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)))``````

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

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!! 🙂

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

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!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors