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

Count number of times something happens in measure

crl_91 — Wed, 20 Mar 2024 14:22:42 GMT

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 :).

Re: Count number of times something happens in measure

Greg_Deckler — Wed, 20 Mar 2024 14:27:59 GMT

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

Re: Count number of times something happens in measure

Anonymous — Wed, 27 Mar 2024 07:08:50 GMT

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.

Re: Count number of times something happens in measure

crl_91 — Wed, 27 Mar 2024 13:46:46 GMT

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

Re: Count number of times something happens in measure

crl_91 — Tue, 16 Apr 2024 12:24:31 GMT

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