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

[Titatovenaar2]

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

[Titatovenaar2]

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]

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

Kind regards,

Igor

[amitchandak]

@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])

[Titatovenaar2]

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

Kind regards,

Igor

[Titatovenaar2]

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