Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
meskens
Frequent Visitor

Countrows/Distinctcount DAX question

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.
 
MaandNaam/voornaamBetaalde urenZiekPercentage
1-1-2023A168:000:000,00
1-1-2023B164:304:002,43
1-1-2023C152:300:000,00
1-1-2023D168:000:000,00
1-2-2023A168:000:000,00
1-2-2023B169:000:000,00
1-2-2023C169:0022:3013,31
1-2-2023D168:000:000,00
1 ACCEPTED SOLUTION

@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.

1.png

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

View solution in original post

6 REPLIES 6
meskens
Frequent Visitor

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?

@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.

1.png

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

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.

meskens
Frequent Visitor

Hi  Tamerj1,

 

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

 

@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
)
)

tamerj1
Super User
Super User

Hi @meskens 

Please try

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.