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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.