Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have multiple tables with a Start Date column and and End Date Column and I would like to filter based on the Date Slicer falling within the range. For example:
Type | Start Date | End Date |
Sick | 2/1/2017 | 3/1/2017 |
I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.
How can I achieve this?
Solved! Go to Solution.
Hi @thmonte,
I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.
If I understand you correctly, you should be able to follow steps below to use a Date Slicer to filter your data on your visual.
1. Add an individual Calendar table if you don't have one yet(make sure not create any relationship between this table and your original table).
Date = CALENDARAUTO()
2. Use the formula below to create a measure, and use it to apply a visual level filter(Count of Type is greater than 0) on your visual.
Count of Type = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, ( Table1[Start Date] <= currentDate && Table1[End Date] >= currentDate ) ) )
Note: just replace 'Table1' with your real table name.
Regards
Hi @thmonte,
I want to be able to set the slicer for 2/15/2017 and have this record show up in my visuals but, the slicer is only able use one column as a filter.
If I understand you correctly, you should be able to follow steps below to use a Date Slicer to filter your data on your visual.
1. Add an individual Calendar table if you don't have one yet(make sure not create any relationship between this table and your original table).
Date = CALENDARAUTO()
2. Use the formula below to create a measure, and use it to apply a visual level filter(Count of Type is greater than 0) on your visual.
Count of Type = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, ( Table1[Start Date] <= currentDate && Table1[End Date] >= currentDate ) ) )
Note: just replace 'Table1' with your real table name.
Regards
@v-ljerr-msft
How to do the relatationship between 'Date' and 'Table1' when on right side you have [Start date] and [End date]?
Between which fields it should be done?
The measure [Count of Type] returs the count of rows in a selection. How it can filter visual ?
Is this impossible to do if I am using DirectQuery? I want to have my data refresh in real time once published.
Hi @thmonte,
Yes, it is. As we cannot add calculate tables in DirectQuery mode, you may need to add the Calender table on your source side instead. And the other steps are the same.
Regards
I think you would need to create a date table, relate it to your other tables based on date
Then create a filter based on date from your date table, and a filter on whatever table has the type
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |