cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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"
    ))

 

 

Capture.PNG 

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. 

Langutang_0-1600128837202.png

This card is [Alert Sum]

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Langutang - OK, I think something like:

Measure = 
  VAR __Date = MAXX(ALL('Table'),[Date])
  VAR __Table = 
    SUMMARIZE(
      FILTER(ALL('Table'),[Date]=__Date),
      [Call Reason],
      "Measure",[Alert] //this is your alert measure
    )
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!




View solution in original post

9 REPLIES 9
Highlighted
Super User IV
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
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
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

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

Highlighted

@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

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

 

Langutang_0-1600130086810.png

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

@Langutang - OK, I think something like:

Measure = 
  VAR __Date = MAXX(ALL('Table'),[Date])
  VAR __Table = 
    SUMMARIZE(
      FILTER(ALL('Table'),[Date]=__Date),
      [Call Reason],
      "Measure",[Alert] //this is your alert measure
    )
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!




View solution in original post

Highlighted

This worked beautifully! Thank you!

Highlighted

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




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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