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
Titatovenaar2
Helper III
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'.

PBIX File 

 

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
Titatovenaar2
Helper III
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?

Titatovenaar2
Helper III
Helper III

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

Kind regards,

Igor

amitchandak
Super User
Super User

@Titatovenaar2 , Try  return with this change

 

 

RETURN
COUNTX( AddColumn(
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])

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

 

Kind regards,

Igor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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