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

[Ajendra]

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

[Ajendra]

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

[Greg_Deckler]

@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

[Ajendra]

@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!

[Greg_Deckler · Accepted solution]

@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

[Greg_Deckler]

@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.