cancel
Showing results 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

Helper III

## DAX: COUNTX has a blank total (.pbix included)

Hi everyone,

I want to be able to count the current number of sick employees via a measure.

To decide whether someone is sick, we look at the 'Fact VZ' table and check whether the date that an employee called in better is lower than the date an employee called in sick.

I use a COUNTX function to iterate over a table where the records are already filtered in such a way that only sick employees are left to iterate over. Therefore simply counting the rows of this table should be enough to return the number of sick employees.

The measure does count correctly within the table, but it does not show a total.

In this example I added 2 employees, of which one is sick and the other is not. The total should be '1' instead of 'BLANK'.

``````ActueelZiek2.0 =
VAR _MaxZiekDatum =
CALCULATE(
MAX('Fact VZ'[datum])
,FILTER('Fact VZ',
'Fact VZ'[FactTypeID] = 13 &&
'Fact VZ'[TypeID] = "AantalZiekmeldingen" &&
'Fact VZ'[Aantal] = 1)
)

VAR _MaxBeterDatum =
CALCULATE(
MAX('Fact VZ'[datum])
,FILTER('Fact VZ',
'Fact VZ'[FactTypeID] = 13 &&
'Fact VZ'[TypeID] = "AantalBetermeldingen" &&
'Fact VZ'[Aantal] = 1
)
)

RETURN
COUNTX(
FILTER(
SUMMARIZE(
FILTER('Fact VZ', 'Fact VZ'[TypeID] IN {"AantalBetermeldingen","AantalZiekmeldingen"} && 'Fact VZ'[FactTypeID] = 13),
'Fact VZ'[MedewerkerID], 'Fact VZ'[FactTypeID]
)
, _MaxZiekDatum >= _MaxBeterDatum
)
,COUNTROWS(DISTINCT('Fact VZ'[MedewerkerID]))
)``````

In the file I've included the dataset. It is all based on 1 table 'Fact VZ'.

In reality this table has around 100 million records, and I already noticed that the performance of my current measure isn't too great. Maybe I am overlooking something else too that can make it way more efficient.

Any help/suggestions are greatly appreciated!

Kind regards,

Igor

5 REPLIES 5
Helper III

Is there anyone who understands why my measure and also the proposed measure by Amitchandak don't count the total, but only delivers result in a table on Row-level?

Helper III

Does anyone have a suggestion to get a result in the measure instead of BLANK()?

Kind regards,

Igor

Super User

@Titatovenaar2 , Try  return with this change

RETURN
FILTER(
SUMMARIZE(
FILTER('Fact VZ', 'Fact VZ'[TypeID] IN {"AantalBetermeldingen","AantalZiekmeldingen"} && 'Fact VZ'[FactTypeID] = 13),
'Fact VZ'[MedewerkerID], 'Fact VZ'[FactTypeID]
)
, _MaxZiekDatum >= _MaxBeterDatum
)
, "_1", calculate( COUNTROWS(DISTINCT('Fact VZ'[MedewerkerID])))
), [_1])

Helper III

Maybe instead of a solution, is there an explanation why it doesn't show a total?

Kind regards,

Igor

Helper III

I noticed that I had to replace the ">=" to ">" in my code.

However, even when I do that, also with your code, it results in exactly the same: in the table at row-level it correctly shows '1', but as a total it shows 'BLANK()'.

Kind regards,

Igor

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors