- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
If I do this for only 1 month with:
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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 )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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 )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Tamerj1,
I get an error message : Function max only accepts a column reference as an argument.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, I thought it was a column. Please try
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT (
MAXX (
CALCULATETABLE (
VALUES ( '2023'[Maand] )
),
[Percentage]
) = 0
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @meskens
Please try
Aantal 0 =
SUMX (
VALUES ( '2023'[Naam/voornaam] ),
INT ( CALCULATE ( MAX ( '2023'[Percentage] = 0 ) ) = 0 )
)

Helpful resources
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.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |