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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.