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

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

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

@Anonymous 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

"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.
Anonymous
Not applicable

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

@Anonymous 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

This worked beautifully! Thank you!

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

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

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