Count number of times something happens in measure

[crl_91]

I would like to count the number of times per date per part that the percentage #good/#done is below 85%, and do this in a measure.

E.g. - 1 jan 2024 for part X: (55+55)/(60+70) = 84,6%
       - 2 jan 2024 for part X: (55+55)/(55+75) = 84,6%

       - 3 jan 2024 for part X: (55+55)/(60+60) = 91,7%

Eventually I would like to count the number of times per month that this happens, and also be able to show exactly on which day this happened. For example:

Month	Number
Jan 2024	2
Feb 2024	5
March 2024	3

And if I then want to show it per day for january for example (and then only show these two rows):

Day	Number
1 jan 2024	1
2 jan 2024	1

Hope anybody can help me :).

[Anonymous · Accepted solution]

Hi @crl_91 ,

I create a table as you mentioned.

Then I create a measure named Percentage. Here is the DAX code.

Percentage =
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _currentPart =
    MAX ( 'Table'[Part] )
VAR _SumGood =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = _currentDate
                && 'Table'[Part] = _currentPart
        ),
        'Table'[#good]
    )
VAR _SumDone =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = _currentDate
                && 'Table'[Part] = _currentPart
        ),
        'Table'[#done]
    )
RETURN
    _SumGood / _SumDone

I create a measure to satisfy your requirements.

MEASURE =
VAR _VirtualTbale =
    SUMMARIZE (
        'Table',
        'Table'[Date],
        'Table'[Part],
        "_percentage", 'Table'[Percentage]
    )
RETURN
    COUNTX ( FILTER ( _VirtualTbale, 'Table'[Percentage] < 0.85 ), [_percentage] )

Best Regards

Yilong Zhou

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

[crl_91]

Hi Yilong,

Thank you so much for your help! However, I needed to add more information to the measure, and now I am stuck again. Could you maybe help me with this one: Count number of times something happens in measure - Microsoft Fabric Community

[crl_91]

It works perfectly :). Thank you for your help!

[Greg_Deckler]

@crl_91 Maybe:

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      SUMMARIZE('Table', [Date], [Part], "__good", SUM('Table'[# good]), "__done", SUM('Table'[# done]),
      "__PercentGood", DIVIDE( __good, __done, 0 )
    )
  VAR __Result = COUNTROWS( FILTER( __Table, [__PercentGood] < .85 )
RETURN
  __Result