Earlier this week I posted a challenge for creating a dynamic date filter using DAX. If you want to see the challenge first and see if you can solve it, check out that post first. If you just want to see how it’s done, read on…
The Challenge
Based on the Date field of when a Client last had an Activity (Last Activity Date), create a Power BI report with a Slicer that lets the user filter Clients based on “Last Active 30 days, 90 days, All Time”
Expected Outcome
Solution
1. Create Table for Slicer
The first thing we need to do is create a list with the values that we can use for our Slicer “Last Active Date”. To do this, we create a new table with 3 default values. Navigate to the Data tab on the left-hand menu, click on Modeling on the ribbon, and then click on New Table.
In the formula bar, enter the following DAX code:
Last Active = DATATABLE(
"Last Active Date", string,
{
{"Last 30 Days"},
{"Last 90 Days"},
{"All Time"}
})
2. Create Measure to Determine Selected Value from Slicer
Now that we have a slicer, we need to store it’s value in a Measure so that we know what data to display to the end user when a value is selected. To do this, we create a new Measure in the Clients table called LastActiveDate:
LastActiveDate = LASTNONBLANK('Last Active'[Last Active Date],TRUE())
3. Create a Measure to Filter the Client data based on Selected Slicer Value
This is where things get “tricky.” You may be wondering, “why can’t I just create a filter directly on the data based on the number of days?” As one reader commented on the last post, something like this:
Activity = switch(true(), datediff(Clients[Last Activity Date],TODAY(),DAY)<30,"30DAYS", datediff(Clients[Last Activity Date],TODAY(),DAY)<90,"90DAYS","ALL")
The reason this doesn’t work is because the filter categorizes the data into 1 of 3 values, when in reality we need the data to be able fall into any of the values.
Consider the scenario of a client who was active last week. If I select “Active Last 30 Days” I would expect to see the client there; if I select “Active Last 90 Days” I would also expect to see that client there; and lastly, if I select “All Time”, essentially removing the filter on number of days, I would expect the client to show up in the results as well.
To get this type of functionality, I created a Measure called FilterLastActive, with the following DAX expression:
FilterLastActive =
CALCULATE( COUNT(Clients[Client Key]),
FILTER(
all (Clients[Days Since Last Activity]),
Clients[Days Since Last Activity] <= if([LastActiveDate]="Last 30 Days", 30,
if([LastActiveDate]="Last 90 Days", 90, 99999)
) ) )
This expression uses a “<=” filter to determine the filter to apply to Clients. The 2 nested IF statements check for Clients last active in the last 30 days first, then last active in the last 90 days, and a default of active in the last 99,999 days if All Time is selected.
4. Apply filter to the visual
Once the measure has been created, we add it to the visual and set a filter on that measure to show items greater than or equal to 1:
It would have been nice to be able to apply the measure as a filter to the entire report. However, this functionality is currently not available in Power BI, so the filter needs to be applied to each visual. Hopefully this gets improved in new releases of the product.
Here’s a link to download the .pbix file of the final solution.
Do you have a different approach or an easier one? I’d like to know how you did it! Feel free to drop a comment below.
