topic Re: I need Maximum number of "Present" for a particular day. in DAX Commands and Tips

I need Maximum number of "Present" for a particular day.

Ajendra — Sun, 19 Mar 2023 01:06:20 GMT

I have some data of attendance. i am trying to perform a measure, So when ever i put that measure along datemaster(calender) in Table it should show only that data whereon max number of people were present.

Date	Status
18-Mar-23	Present
18-Mar-23	Present
18-Mar-23	Present
15-Mar-23	Present
14-Mar-23	Absent
12-Mar-23	Present
10-Mar-23	HD
06-Mar-23	Late
02-Mar-23	Present
26-Feb-23	Present
22-Feb-23	Present
20-Feb-23	Present
19-Feb-23	Present

Thanks & Regards,

Ajendra Singh Rawat

Re: I need Maximum number of "Present" for a particular day.

Greg_Deckler — Sun, 19 Mar 2023 03:22:34 GMT

Ajendra Sorry, can you elaborate a bit on this? So, you want to show March 18th data? Do you want to just display 18-Mar-23 or ?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: I need Maximum number of "Present" for a particular day.

Ajendra — Mon, 20 Mar 2023 16:07:20 GMT

Hi,

i am new here may be not aware of how shoud i ask question or query.

Greg_Deckler thanks a ton. for replying and your precious suggetions

here are some point please go through.

1. I want to achive this through the Measure only.

2. i want to use that measure in table which already has a date range.

Consider below image as output table where Measure has been placed:

Consider below image as raw data table.

Thanks & Regards,

Ajendra Singh

Re: I need Maximum number of "Present" for a particular day.

Greg_Deckler — Mon, 20 Mar 2023 16:57:25 GMT

Ajendra OK, try this:

Measure = VAR __Date = MAX('Table'[Date]) VAR __Table = SUMMARIZE(FILTER(ALL('Table'), [Status] = "Present"),[Date],"__Count",COUNTROWS('Table')) VAR __MaxCount = MAXX(__Table,[__Count]) VAR __MaxCountDate = MAXX(FILTER(__Table,[__Count] = [__Count]),[Date]) VAR __Result = IF( __Date = __MaxCountDate, __MaxCount, BLANK()) RETURN __Result

Re: I need Maximum number of "Present" for a particular day.

Ajendra — Mon, 20 Mar 2023 19:28:16 GMT

Greg_Deckler Thanks for the support i really appreciate your efforts.

but it is looking bit off.

Because i need Date that should be based on Maximum number of "Present"

not Max date and its No. of "Present"

Thanks!

Re: I need Maximum number of "Present" for a particular day.

Greg_Deckler — Mon, 20 Mar 2023 21:10:58 GMT

Ajendra That is what it should be doing, I'll walk you through the code:

Measure = /* Get the current date in context assuming you have included Date in your visual */ VAR __Date = MAX('Table'[Date]) /* Return a table filtered to "Present" status This table is grouped by Date and __Count column has the count of rows */ VAR __Table = SUMMARIZE(FILTER(ALL('Table'), [Status] = "Present"),[Date],"__Count",COUNTROWS('Table')) /* Next, we return the maximum count in __Table */ VAR __MaxCount = MAXX(__Table,[__Count]) /* We now return the date that corresponds with the maximum count Note, we could have used MINX here as well. Doesn't matter really. */ VAR __MaxCountDate = MAXX(FILTER(__Table,[__Count] = [__Count]),[Date]) /* Now, if the current date (__Date) matches the date for our maximum count, we return the maximum count. Otherwise we return BLANK() */ VAR __Result = IF( __Date = __MaxCountDate, __MaxCount, BLANK()) RETURN __Result