Forum Discussion

Musker's avatar
Musker
Frequent Visitor
2 years ago
Solved

10 or more occurances total count

Hey all,


I previously received help on a different thread couple months ago (Re: Formula to do count of results in an hour time... - Microsoft Fabric Community)

I got this working, but my problem is now rather than counting the days when there was 10 or more occurances, I need it to count the total times there was 10 or more occurances. 

Currently it's operating off of the below, which like I said works in terms of telling me how many days there was 10 or more occurances, but I need to change it to tell me the overall times there was 10 or more occurances in an hour

 

DaysCount_Low > 10 New = CALCULATE( DISTINCTCOUNT( 'Main Data'[DateOnly] ),
FILTER( 'Main Data', 'Main Data'[InitialSeverity] = "Low" &&
'Main Data'[InitialSeverity_Count] >= 10 )) + 0
 
I can't seem to attach the file, but if example data is needed please let me know. Thanks 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Musker ,

     

    I suggest you to try code as below to create a measure.

    DaysCount_Low > 10 New =
VAR _ADD =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DatewithHourOnly",
                DATEVALUE ( 'Table'[RaiseTime] )
                    + HOUR ( 'Table'[RaiseTime] ) / 24
        ),
        [InitialSeverity] = "Low"
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADD, [DatewithHourOnly], "Count", COUNT ( 'Table'[RaiseTime] ) )
RETURN
    COUNTX ( FILTER ( _SUMMARIZE, [Count] >= 10 ), [DatewithHourOnly] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    2 years ago

    Hey Musker 

     

    it would be great if you share sample of your data with name of columns and data in each one, but try this:

     

    measure:=
    countrows(

    summarize(FILTER( 'Main Data', 'Main Data'[InitialSeverity] = "Low" &&
    'Main Data'[InitialSeverity_Count] >= 10 ),

    Data'[InitialSeverity]

    )

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!! 

    LinkedIn | Twitter | Blog | YouTube 

  • Musker's avatar
    Musker
    Frequent Visitor
    2 years ago

    Hey VahidDM - I did send a reply but it's disappeared, apologies if I'm double sending.

    Thanks for the reply and help, it doesn't seem to work as returns '1'.

    I've added some sample data to the shared sheet - with this and my current measure, I get '2' as an answer whereas I would expect '7' as that's how many times there was 10 or more Low occurances within an hour.

    Thanks 

     

    Sample Data 

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      2 years ago

      I just read your question again and found that you want that based on each hour, correct?

       

      Can you post sample expected output?

      please see this post regarding How to Get Your Question Answered Quickly:
      https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

      The most important parts are:
      1. Sample data as text, use the table tool in the editing bar
      2. Expected output from sample data
      3. Explanation in words of how to get from 1. to 2.
      4. Relation between your tables

      Appreciate your Kudos!!
      LinkedIn:www.linkedin.com/in/vahid-dm/

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Musker ,

       

      I suggest you to try code as below to create a measure.

      DaysCount_Low > 10 New =
VAR _ADD =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "DatewithHourOnly",
                DATEVALUE ( 'Table'[RaiseTime] )
                    + HOUR ( 'Table'[RaiseTime] ) / 24
        ),
        [InitialSeverity] = "Low"
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADD, [DatewithHourOnly], "Count", COUNT ( 'Table'[RaiseTime] ) )
RETURN
    COUNTX ( FILTER ( _SUMMARIZE, [Count] >= 10 ), [DatewithHourOnly] )

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.