cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: DAX Help

 

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

 

 

Best

D



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

Highlighted
Super User IV
Super User IV

Re: DAX Help

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: DAX Help

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


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper IV
Helper IV

Re: DAX Help

Hi @darlove ,

 

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

 

IMG20200527134204.jpg

 

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

Highlighted
Super User IV
Super User IV

Re: DAX Help

 

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

 

 

Best

D



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

Highlighted
Super User IV
Super User IV

Re: DAX Help

If you don't want to calculate wrong numbers from time to time... you have to change the design into a proper star-schema. Otherwise you're risking incorrect numbers without even knowing it.

If you want to know what can happen when you have just one big table as the model, please read this:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Best
D


Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


Highlighted
Helper IV
Helper IV

Re: DAX Help

@darlove ,

 

Thank you. You have found solution for me. 

 

In other case, I have Strugling to calculate total Child who has absent greater than 10% to less than or equal 20%.

Please look at this picture:

 

IMG20200527190624.jpg

 

In Values, I have created this measure:

# of Absent Child = CALCULATE (COUNTA('AttendanceMaster'[ChildID], 'AttendanceMaster[IsPresent] IN { FALSE })

 

After that I created this measure too:

# of Child Days = CALCULATE(COUNT('AttendanceMaster'[ChildID]))

 

Than I created this measure also:

% of Absent Child = CALCULATE(DIVIDE([# of Absent Child],[# of Child Days]))

 

I want to measure total count of Child who has absent >10% to <=20% every month. 

Can you help me again for the last? 

I have attach pbix on this link. Please look that data:

Updated Data Cleaned 

 

Thank you for your help

 

Best

Highlighted
Super User IV
Super User IV

Re: DAX Help

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



Have I answered your question?
Please mark my post as the solution.


Please don't forget your Kudos if you don't mind. Thanks.


View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors