Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Help

Hi, 

 

Please help me with Power BI DAX. 

I have strugling to create DAX to count total number of Child who has absent 3 or more days every month.

 

Please help me. 

I have attached my cleaned data on this link:

Data Cleaned 

 

Thank you for your help

DAX function
Dax Help
Help Requested
Help with DAX
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    # Child Absent 3+ Days = 
COUNTROWS(
    FILTER(
        VALUES( AttendanceMaster[ChildID] ),
        CALCULATE(
            COUNTROWS( AttendanceMaster ) >= 3,
            KEEPFILTERS( NOT AttendanceMaster[IsPresent] )
        )
    )
)

     

     

    Best

    D

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi there.

     

    I've had a look at the file. I think many of those measures are incorrect (and will not work correctly in many filtering conditions) but I might be wrong and from your point of view they are correct. Not sure but my experience tells me they are not right.

     

    However, regardless of the above, I think you're after such a measure:

    # Children 10%-20% = 
    COUNTROWS(
        FILTER(
            VALUES( AttendanceMaster[ChildID] ),
            .1 < [% of Absent Child]
            &&
            [% of Absent Child] <= .2
        )
    )

    If this does not work, you have to create a measure (in place of [% of Absent Child]) that will calculate the percentage of time that a child was absent IN ANY SETTING. Please bear in mind that the measure should work correctly under any circumstances. If it's not, then the calculation is wrong as well.

     

    Hope this helps.

     

    Best

    D

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    The formulation of the problem is not precise. Please give examples of calculations and what you mean by "absent 3 or more days every month" in some contexts.

    Best
    D
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      I have updated the pbix please look at attachment and this pictute:

       

       

      In Values I have added this DAX:

      # of Absent = CALCULATE(COUNTA (AttendanceMaster[ChildID], FILTER (AttendanceMaster,AttendanceMaster[IsPresent] = FALSE ()))

       

      I want to measure total ChildID who have values 3 or more days every month. So that I have to created this DAX but does not work. 

      # of ChildID Absent 3 or more days = CALCULATE(COUNTA (AttendanceMaster[ChildID], FILTER (AttendanceMaster,[# of Absent] >= 3))

       

      Please look at this link:

      Data Cleaned 

       

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

        # Child Absent 3+ Days = 
COUNTROWS(
    FILTER(
        VALUES( AttendanceMaster[ChildID] ),
        CALCULATE(
            COUNTROWS( AttendanceMaster ) >= 3,
            KEEPFILTERS( NOT AttendanceMaster[IsPresent] )
        )
    )
)

         

         

        Best

        D