Finding the Most Repeated Date using Multiple Criteria

Question

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 · Answer

Austen229022 , Create a Measure and use Index function to get the top row&nbsp;
&nbsp;
M1= countrows(Table)&nbsp;
&nbsp;
Top Date
maxx(index(1, allselected(Date[Date], orderBy([M1], desc)), [Date])
&nbsp;
&nbsp;
Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

austen229022 · Answer

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 · Answer

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

austen229022 · Answer

Hi Ashish,&nbsp;&nbsp;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.&nbsp;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.&nbsp;DateRegime01/01/2023AB01/01/2023CD05/02/2023AB01/01/2023AB05/01/2023CD05/01/2023CD01/01/2023AB06/05/2023CD01/01/2023AB&nbsp;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&nbsp;Hopefully this makes sense

ashish_mathur · Answer

Hi,
PBI file attached.
Hope this helps.

&nbsp;

Forum Discussion

Finding the Most Repeated Date using Multiple Criteria

5 Replies

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Date difference with multiple criteria