Count number of times something happens in measure

[crl_91]

Hi all,

I already had a ton of help from the community on my question, but I got stuck again (see this question for the earlier help: Solved: Count number of times something happens in measure - Microsoft Fabric Community)

I would like to count the number of times per date per part that the percentage #good/#done is below 75% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 10%, and do this in a measure. When either the #good/#done is below 75% or (#planned - #done)/#planned is above 10% I call this a black day.

In another measure I would like to calculate the same, however I would like to count the number of times per date per part that the percentage #good/#done is below 85% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 5%, and do this in a measure. When either the #good/#done is below 85% or (#planned - #done)/#planned is above 5% I call this a red day. However, when something is already categorized as a black day, it cannot be a red day anymore. 

E.g. #good/#done: - 1 jan 2024 for part X: (55+55)/(60+70) = 84,6%
                               - 2 jan 2024 for part X: (55+55)/(55+100) = 71,0%

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

                               - 1 feb 2024 for part X: (62+60)/(60+70) = 93,8%

E.g. (#planned - #done)/#planned: - 1 jan 2024 for part X: (60+70 - (60+70)) / (60+70) = 0%
                                                         - 2 jan 2024 for part X: ((80+90) - (55+100)) / (80+90) = 8,8%

                                                         - 3 jan 2024 for part X: ((60+75) - (60+60)) / (60+75) = 11,1%

                                                         - 1 feb 2024 for part X: ((60+70) - (60+70)) / (60+70) = 0%

Therefore the days will be qualified as follows: - 1 jan 2024 for part X: red OR no qualification, so red

                                                                           - 2 jan 2024 for part X: black or red, so black

                                                                           - 3 jan 2024 for part X: no qualification or black, so black

                                                                           - 1 feb 2024 for part X: no qualification or no qualification, so do not count

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 for part X:

Month	Black day	Red day
Jan 2024	2	1
Feb 2024	2	0
March 2024 (just as example added)	5	1

And if I then want to show it per day for january for example (and then only show the rows with a red or black day):

Day	Black day	Red day
1 jan 2024		1
2 jan 2024	1
3 jan 2024	1
2 feb 2024	1
3 feb 2024	1

What I did so far is:
Create a measure to calculate #good/#done

Percentage punct =

VAR _currentDatePunc =

    MAX ( 'Table'[Date] )

VAR _currentPartPunc =

    MAX ( 'Table'[Part] )

VAR _SumGoodPunc =

    SUMX (

        FILTER (

            ALLSELECTED ( 'Table' ),

            'Table'[Date] = _currentDatePunc

                && 'Table'[Part] = _currentPartPunc

        ),

        'Table'[#good]

    )

VAR _SumDonePunc =

    SUMX (

        FILTER (

            ALLSELECTED ( 'Table' ),

            'Table'[Date] = _currentDatePunc

                && 'Table'[Part] = _currentPartPunc

        ),

        'Table'[#done]

    )

RETURN

    _SumGoodPunc / _SumDonePunc

Create a measure to calculate (#planned - #done)/#planned

Percentage Uitv =

VAR _currentDateUitv =

    MAX ( 'Table'[Date] )

VAR _currentPartUitv =

    MAX ( 'Table'[Part] )

VAR _SumGeplandUitv =

    SUMX (

        FILTER (

            ALLSELECTED ( 'Table' ),

            'Table'[Date] = _currentDateUitv

                && 'Table'[Part] = _currentPartUitv

        ),

        'Table'[#planned]

    )

VAR _SumDoneUitv =

    SUMX (

        FILTER (

            ALLSELECTED ( 'Table' ),

            'Table'[Date] = _currentDateUitv

                && 'Table'[Part] = _currentPartUitv

        ),

        'Table'[#done]

    )

RETURN

    (_SumGeplandUitv - _SumDoneUitv) / _SumGeplandUitv

Create a measure for #good/#done red days:

MEASURE Punc RD =

VAR _VirtualTbalePunc =

    SUMMARIZE (

        'Table',

        'Table'[Date],

        'Table'[Part],

        "_percentage", 'Table'[Percentage punct]

    )

RETURN

    COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage punct] < 0.85 && 'Table'[Percentage punct] >= 0.75), [_percentage] )

Create a measure for (#planned - #done)/#planned red days:

MEASURE Uitv RD =

VAR _VirtualTbalePunc =

    SUMMARIZE (

        'Table',

        'Table'[Date],

        'Table'[Part],

        "_percentage", 'Table'[Percentage Uitv]

    )

RETURN

    COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage Uitv] > 0.05 && 'Table'[Percentage Uitv] <= 0.1), [_percentage] )

But then I am stuck, so hope anybody can help me further :).

Thanks in advance.

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.