How to Filter Distinct Date Using Power BI DAX?

Sometimes, you may need to display unique dates from a dataset in a Power BI report. You can create a calculated table or column that filters out duplicate dates to do this. Let’s look at an example of how to filter distinct date using Power BI DAX.

Filter Distinct Date Using Power BI DAX

While working on Power BI, I was required to create a new table from a table named DateTable that includes a column called Date containing various dates. The new table should only include a unique list of dates from before today.

Check the below screenshot for DateTable:

Now follow the below steps:

Open Power BI Desktop and load the above DateTable. Then, you can see the data presented in the Data pane.

Go to the Modeling tab and Click on New table.

In the formula bar, put the DAX expression below.

FilteredDates = 
VAR TodayDate = TODAY()
RETURN
DISTINCT (
    FILTER (
        'DateTable',
        'DateTable'[Date] < TodayDate
    )
)

Where:

• FilteredDates: This is the name of the new table.
• VAR TodayDate = TODAY(): This defines a variable TodayDate that holds today’s date.
• RETURN: This specifies what the formula should return after defining the variables.
• DISTINCT: This function returns only unique values in the resulting table.
• FILTER(‘DateTable’, ‘DateTable'[Date] < TodayDate): This filters the DateTable to include only rows where the Date is less than today’s date.

Now, you can see that the table’s date column does not have repeated dates. This is how you can filter distinct dates using Power BI DAX.

Distinct Count of Values Based on Date using Power BI DAX

Recently, I received a requirement to analyze the number of unique IDs for specific dates and locations. The goal was to display a distinct count of IDs for the dates selected from a date slicer and a particular market location chosen from a location slicer.

I loaded a demo date set in Power BI according to the requirement.

Let’s follow the below steps to do this:

In the Report view, add two Power BI Slicer visuals. One is the Date Slicer, which shows all the dates, and the other is the Location Slicer, which shows all the Locations.

Go to the Modeling tab and Click on New measure. Then, in the formula bar, put the DAX expression below.

DistinctIDCount = 
VAR SelectedDates = VALUES('AssetTracking'[Date])
VAR SelectedLocation = SELECTEDVALUE('AssetTracking'[Location])
RETURN
    CALCULATE(
        DISTINCTCOUNT('AssetTracking'[ID]),
        'AssetTracking'[Date] IN SelectedDates,
        'AssetTracking'[Location] = SelectedLocation
    )

Where:

• DistinctIDCount: This is the name of the new measure.
• VAR SelectedDates = VALUES(‘AssetTracking'[Date]): This variable captures the list of unique dates currently selected in the AssetTracking table.
• VAR SelectedLocation = SELECTEDVALUE(‘AssetTracking'[Location]): This variable captures the single selected value from the Location column in the AssetTracking table.
• RETURN: This specifies what the formula should return after defining the variables.
• CALCULATE: This function evaluates an expression in a modified filter context.
• DISTINCTCOUNT(‘AssetTracking'[ID]): This expression counts the number of unique IDs in the AssetTracking table.
• ‘AssetTracking'[Date] IN SelectedDates: This condition ensures that only rows with dates that match the selected dates are considered.
• ‘AssetTracking'[Location] = SelectedLocation: This condition ensures that only rows with the selected location are considered.

Then, add a Power BI Card visual and the DistinctIDCount measure.

When you select any Date and Location from the Power BI Slicer, the distinct ID count is displayed in the Power BI card visual.

This way, you can distinguish the count of values based on a date using Power BI DAX.

Additionally, you may like some more Power BI articles:

• Filter Date Using Power BI DAX
• Power BI DAX ISBLANK vs ISEMPTY
• Power BI DAX Count
• Append Columns in Power BI using Power Query Editor

Conclusion

I hope you followed all the steps and now know how to filter distinct dates in Power BI using DAX. This tutorial covered filtering by distinct dates using Power BI DAX and the distinct count of values based on dates using Power BI DAX.