Forum Discussion

meskens's avatar
meskens
Frequent Visitor
3 years ago
Solved

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: 

Percentage =divide('2023'[Ziek],'2023'[Betaalde uren])
 

If I do this for only 1 month with:

Aantal 0 = COUNTROWS(FILTER('2023','2023'[Percentage]=0)) it works but when I select more then 1 month it's add up.
But I only want in case of selecting multiple months on the slicer that still have an average percentage of 0.
I tried it with distinctcount but for some reason it gives me the same result.
See below for some data.
Result when selecting january on the slicer should be: 3, result of selecting january and february on the slicer should be 2.
 
Thanks in advance for your help.
 
MaandNaam/voornaamBetaalde urenZiekPercentage
1-1-2023A168:000:000,00
1-1-2023B164:304:002,43
1-1-2023C152:300:000,00
1-1-2023D168:000:000,00
1-2-2023A168:000:000,00
1-2-2023B169:000:000,00
1-2-2023C169:0022:3013,31
1-2-2023D168:000:000,00
  • tamerj1's avatar
    tamerj1
    3 years ago

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi meskens 

    Please try

    Aantal 0 =
    SUMX (
    VALUES ( '2023'[Naam/voornaam] ),
    INT ( CALCULATE ( MAX ( '2023'[Percentage] = 0 ) ) = 0 )
    )

  • meskens's avatar
    meskens
    Frequent Visitor
    3 years ago

    Hi  Tamerj1,

     

    I get an error message : Function max only accepts a column reference as an argument.

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      meskens 

      Ok, I thought it was a column. Please try

      Aantal 0 =
      SUMX (
      VALUES ( '2023'[Naam/voornaam] ),
      INT (
      MAXX (
      CALCULATETABLE (
      VALUES ( '2023'[Maand] )
      ),
      [Percentage]
      ) = 0
      )
      )

  • meskens's avatar
    meskens
    Frequent Visitor
    3 years ago

    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 

    Aantal 0-3.5 = SUMX ( VALUES ( '2023'[Naam/voornaam] ), INT ( MAXX ( CALCULATETABLE ( VALUES ( '2023'[Maand] ) ), [Percentage] ) > 0 && [Percentage]<3.5 ) )
    It counts everything > 0 but not <3.5
     
    What am I doing wrong?
    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      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 )
)
      • meskens's avatar
        meskens
        Frequent Visitor
        3 years ago

        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.