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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors