Finding the Most Repeated Date using Multiple Criteria

[Austen229022]

Having a small issue as I am looking at finding the date that appears most in my data. This then filters another measure to count the amount of times that date appears.

This works though whenever I filter on the Regime slicer (that has 2 items) it mostly shows the correct date though the second measure isnt showing the correct amount. (see below)
This should count the amount of times that date has appeared based on the Date and the Regime Slicer

Here is the code used:

Max Date Count =

VAR T1 =

        SUMMARIZE('Applications','Applications'[Date Filter],"COUNT",COUNTROWS('Applications'))

VAR MostRepeated =

    MAXX(T1,[COUNT])

Return

   MAXX(T1,IF([COUNT] = MostRepeated,'Applications'[Date Filter]))

Lodgement Days =

VAR MaxDate = [Max Date Count]

RETURN

CALCULATE(COUNTROWS(Applications),FILTER('Applications', 'Applications'[Lodgement On] = MaxDate))

[amitchandak]

@Austen229022 , Create a Measure and use Index function to get the top row 

M1= countrows(Table) 

Top Date

maxx(index(1, allselected(Date[Date], orderBy([M1], desc)), [Date])

Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

[Austen229022]

Hey thank you for your response!

Unfortunately this does not bring up what I am trying to achieve. I have found that I am needing to update the Max Date Count measure so ensure that the correct date is shown based off the Regime slicer - any thoughts?

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Austen229022]

Hi Ashish, 

As you can see in the photo the graph above shows how many applications come in each day of the week, and the card is to show what date showed up the most (which as you can see in the pic is incorrect). The other card takes that date and gives a value of how many times it occured.

What I have found is that the code is taking the overall amount of times a date is showing up, rather than how many times it comes up based on another slicer. This is where the issue lies.

Date	Regime
01/01/2023	AB
01/01/2023	CD
05/02/2023	AB
01/01/2023	AB
05/01/2023	CD
05/01/2023	CD
01/01/2023	AB
06/05/2023	CD
01/01/2023	AB

As you can see from the table, 01/01/2023 would be the date that shows up the most, though the Regime it corresponds to isnt filtered. so AB would show 4 and when the slicer is changed to CD it would show 05/01/2023 appeared 2 times

Hopefully this makes sense

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.