Count number of times something happens in measure

crl_91 — Mon, 08 Apr 2024 14:56:03 GMT

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.

Re: Count number of times something happens in measure

lbendlin — Sat, 27 Apr 2024 01:34:14 GMT

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

topic Re: Count number of times something happens in measure in DAX Commands and Tips

Count number of times something happens in measure

Re: Count number of times something happens in measure