topic Re: Countrows/Distinctcount DAX question in DAX Commands and Tips

Countrows/Distinctcount DAX question

meskens — Wed, 07 Jun 2023 11:59:40 GMT

Hi all,

Maybe you could help me with this one.

I'm trying to how many employees are in a certain category of average sickness (for example 0), based on the months selected in the slicer.

I have a calculation in a calculated column of the table where I calculate the percentage of each employee for each month:

Percentage =divide('2023'[Ziek],'2023'[Betaalde uren])

If I do this for only 1 month with:

Aantal 0 = COUNTROWS(FILTER('2023','2023'[Percentage]=0)) it works but when I select more then 1 month it's add up.

But I only want in case of selecting multiple months on the slicer that still have an average percentage of 0.

I tried it with distinctcount but for some reason it gives me the same result.

See below for some data.

Result when selecting january on the slicer should be: 3, result of selecting january and february on the slicer should be 2.

Thanks in advance for your help.

Maand	Naam/voornaam	Betaalde uren	Ziek	Percentage
1-1-2023	A	168:00	0:00	0,00
1-1-2023	B	164:30	4:00	2,43
1-1-2023	C	152:30	0:00	0,00
1-1-2023	D	168:00	0:00	0,00
1-2-2023	A	168:00	0:00	0,00
1-2-2023	B	169:00	0:00	0,00
1-2-2023	C	169:00	22:30	13,31
1-2-2023	D	168:00	0:00	0,00

Re: Countrows/Distinctcount DAX question

tamerj1 — Wed, 07 Jun 2023 13:35:56 GMT

Hi meskens

Please try

Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( CALCULATE ( MAX ( '2023'[Percentage] = 0 ) ) = 0 )
)

Re: Countrows/Distinctcount DAX question

meskens — Wed, 07 Jun 2023 14:40:27 GMT

Hi Tamerj1,

I get an error message : Function max only accepts a column reference as an argument.

Re: Countrows/Distinctcount DAX question

tamerj1 — Wed, 07 Jun 2023 14:48:00 GMT

meskens

Ok, I thought it was a column. Please try

Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT (
MAXX (
CALCULATETABLE (
VALUES ( '2023'[Maand] )
),
[Percentage]
) = 0
)
)

Re: Countrows/Distinctcount DAX question

meskens — Thu, 08 Jun 2023 06:42:17 GMT

Hi tamerj1 ,

This works great!

One more question, 1 have two more categories:

>0 and <3,5

>= 3,5

However when I change youre code to

SUMX (VALUES ( '2023'[Naam/voornaam] ),INT (MAXX (CALCULATETABLE (VALUES ( '2023'[Maand] )),[Percentage]) >3.5))

It gives no number (I have more date in my own power bi) Should have been 1

When I change the code to

Aantal 0-3.5 = SUMX ( VALUES ( '2023'[Naam/voornaam] ), INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) > 0 && [Percentage]<3.5 ) )

It counts everything > 0 but not <3.5

What am I doing wrong?

Re: Countrows/Distinctcount DAX question

tamerj1 — Thu, 08 Jun 2023 07:17:45 GMT

meskens
The > 3.5 seems to be working. Not sure why you're facing issues with it. I prepared a quick sample file for testing the 3 formulas.

Aantal 0 = SUMX ( VALUES ( '2023'[Naam/voornaam] ), INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) = 0 ) )

Aantal > 3.5 = SUMX ( VALUES ( '2023'[Naam/voornaam] ), INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) > 3.5 ) )

Aantal 0-3.5 = SUMX ( VALUES ( '2023'[Naam/voornaam] ), VAR MaxPercxent = MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) RETURN INT ( MaxPercxent > 0 && MaxPercxent < 3.5 ) )

Re: Countrows/Distinctcount DAX question

meskens — Thu, 08 Jun 2023 09:08:19 GMT

Thanks tamerj1 , all works now. I don't know why in first place it didn't work.

I'll look into your code to understand how it works.