Forum Discussion
Filter Visual by Date Slicer without Showing Date
I'm sorry if this has been asked before, but I can't seem to find a thread that gets at what I need.
I have data that shows client changes with start/end dates, so there are no set periods. The basic set up is below:
| Client | Property | EffectiveDate | EndDate |
| A | House | 1/1/2022 | 2/3/2022 |
| A | Condo | 2/4/2022 | 4/21/2024 |
| B | Apartment | 5/9/1956 | 12/31/2099 |
| C | House | 4/3/1995 | 5/2/2021 |
I want to allow my users to select a date to see how many clients were in each type of proprerty at that date. I have a calendar table (called DATE) that has no relation to the above table. There's a slicer on the page that allows them to select a single date from DATE. I have the below calculation to filter to the selected date:
DATE TF_M = if(MAX('DATE'[Date])>=SELECTEDVALUE(History[EffectiveDate]) && MAX('DATE'[Date])<=SELECTEDVALUE(History[EndDate]),1,0)
This works fine in a table with the clients/properties listed. However, I want to show a bar graph of property type by count of clients, no date involvement other than with filtering the visual. The issue I'm running into is I can't use the above as a filter on the visual. So, I tried creating a calculation to count clients instead:
VAR SelectedYear=SELECTEDVALUE('DATE'[Date])
RETURN
Calculate(DISTINCTCOUNT(History[Client]), FILTER('History', History[EffectiveDate]<=SelectedYear || SelectedYear<=History[EndDate]) )
However, I'm still seeing property types that fall outside my date filter parameters. Why I'm seeing those property types makes sense, since they're not being filtered, but that begs the question, how do I filter those property type down? How can I amend my measure to filter the graph without showing each client line?
Note: The data goes back quite a ways so it's not practical to rework the data to have a line for each day for each client/property.
try
VAR SelectedYear=SELECTEDVALUE('DATE'[Date]) RETURN Calculate(DISTINCTCOUNT(History[Client]), History[EffectiveDate]<=SelectedYear && ( SelectedYear < History[EndDate] || ISBLANK(History[EndDate]) )
2 Replies
- johnt75
Super User
try
VAR SelectedYear=SELECTEDVALUE('DATE'[Date]) RETURN Calculate(DISTINCTCOUNT(History[Client]), History[EffectiveDate]<=SelectedYear && ( SelectedYear < History[EndDate] || ISBLANK(History[EndDate]) )- AnonymousNot applicable
Those && and || will get you every time. Thank you so much, you're a life saver!