Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Finding Most Amount of Times a Value Appears

Currently there is a slicer that is showing the last 1 month of data, this counts all the dates in the Date Received column to find out how many times dates within that range appear.

What I am wanting to achieve is count how many times a certain date appears and show the most times that it occurs

EG:

 Date 01/01/2023 01/01/2023 04/01/2023 14/01/2023

This would show that the most times that a date appears (01/01/2023) is 2

1 ACCEPTED SOLUTION
Community Champion

To count how many times a certain date appears and show the most times it occurs in Power BI, you can use a combination of measures and visuals. Here's how you can achieve this:

1. Create a New Measure:

• Go to the "Modeling" tab in Power BI.
• Click on "New Measure."
• Use the following DAX formula to count the occurrences of each date in your Date column:

Date Count = COUNTROWS(FILTER(YourTable, YourTable[Date] = MAX(YourTable[Date])))

1. Replace "YourTable" with the name of your data table and "Date" with the name of your date column.

2. Create a Table Visual:

• Add a table visual to your report.
• In the table visual, add the "Date" column and the "Date Count" measure you just created.
3. Sort the Table Visual:

• Click on the "Date Count" column in the table visual.
• In the "Visualizations" pane, under "Sort," select "Descending."
4. Display the Top Date:

• To display the date with the highest count, you can create another measure to fetch the corresponding date. Create a new measure with the following DAX formula:

Most Frequent Date =
VAR MaxCount = MAX(YourTable[Date Count])
RETURN
CALCULATE(MAX(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MaxCount))

1. Replace "YourTable" with the name of your data table.

2. Display the Most Frequent Date:

• Add a card visual to your report.
• In the card visual, add the "Most Frequent Date" measure you just created.

Now, when you use the slicer to select the last 1 month of data, the table visual will display the dates and their respective counts in descending order, showing the date with the most occurrences at the top. The card visual will display the most frequent date.

This setup allows you to dynamically find and display the date that appears the most within the selected date range.

6 REPLIES 6
Community Champion

To count how many times a certain date appears and show the most times it occurs in Power BI, you can use a combination of measures and visuals. Here's how you can achieve this:

1. Create a New Measure:

• Go to the "Modeling" tab in Power BI.
• Click on "New Measure."
• Use the following DAX formula to count the occurrences of each date in your Date column:

Date Count = COUNTROWS(FILTER(YourTable, YourTable[Date] = MAX(YourTable[Date])))

1. Replace "YourTable" with the name of your data table and "Date" with the name of your date column.

2. Create a Table Visual:

• Add a table visual to your report.
• In the table visual, add the "Date" column and the "Date Count" measure you just created.
3. Sort the Table Visual:

• Click on the "Date Count" column in the table visual.
• In the "Visualizations" pane, under "Sort," select "Descending."
4. Display the Top Date:

• To display the date with the highest count, you can create another measure to fetch the corresponding date. Create a new measure with the following DAX formula:

Most Frequent Date =
VAR MaxCount = MAX(YourTable[Date Count])
RETURN
CALCULATE(MAX(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MaxCount))

1. Replace "YourTable" with the name of your data table.

2. Display the Most Frequent Date:

• Add a card visual to your report.
• In the card visual, add the "Most Frequent Date" measure you just created.

Now, when you use the slicer to select the last 1 month of data, the table visual will display the dates and their respective counts in descending order, showing the date with the most occurrences at the top. The card visual will display the most frequent date.

This setup allows you to dynamically find and display the date that appears the most within the selected date range.

Helper I

Is there a way that I can use this to also have it filter based on another column as well? Im needing to filter on the date while also filtering on whether a Regime column is one of two items

Something like:

SUMMARIZE('Applications','Applications'[Date Filter],"COUNT",AND(COUNTROWS('Applications'), ...))
Helper I

I currently have a measure that finds the date that comes up the most, though now I am not able to get a COUNT on it whether using COUNTROWS/COUNTX etc

my measure to find the date is as follows:

Measure - Max Date Count =
VAR T1 =
SUMMARIZE('EW Applications','EW Applications'[Date Received],"COUNT",COUNTROWS('EW Applications'))
VAR MostRepeated =
MAXX(T1,[COUNT])
Return
MAXX(T1,IF([COUNT] = MostRepeated,'EW Applications'[Date Received]))

Helper I

Thank you for the reply!

Do you know if this would also work for finding the lowest amount of times a value occurs?

eg: Changing the MAX in the DAX code to MIN like below

Least Frequent Date =
VAR MinCount = MIN(YourTable[Date Count])
RETURN
CALCULATE(MIN(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MinCount))

Community Champion

Yes, you can modify the DAX code to find the date with the lowest count, just as you've suggested. Here's the modified DAX code:

Least Frequent Date =
VAR MinCount = MIN(YourTable[Date Count])
RETURN
CALCULATE(MIN(YourTable[Date]), FILTER(YourTable, YourTable[Date Count] = MinCount))

This code will calculate the date that appears the least number of times in your data based on the "Date Count" column (assuming you have such a column representing the count of each date). Make sure to replace 'YourTable' with the actual name of your table and 'Date Count' with the actual name of your count column.

You can use the "Least Frequent Date" measure in a visual, like a Card or a Table, to display the date with the lowest count in your Power BI report.

Helper I

I have done this though for whatever reason it isnt showing the least occuring though showing the first of the month (I am filtered on a month) how can I fix this?

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors