Forum Discussion
Use a Date Slicer to Control a Filter
I have data that looks like:
| State | County | First_Scraped | Last_Scraped | Date | ID |
| NY | New York | 2020-01-01 | 2021-07-01 | 2020-01-01 | xyz |
| NY | New York | 2020-01-01 | 2021-07-01 | 2020-01-02 | xyz |
| ... | |||||
| NY | New York | 2020-01-01 | 2021-07-01 | 2021-07-01 | xyz |
| NY | New York | 2020-01-01 | 2021-07-01 | 2020-01-01 | abc |
| ... | |||||
| CA | Los Angeles | 2020-05-25 | 2021-03-20 | 2020-05-25 | jkl |
| CA | Los Angeles | 2020-05-25 | 2021-03-20 | 2020-05-26 | jkl |
| ... |
I want to create a line graph with a date slicer, where the line graph has Date as the x-axis and count(ID) as the y-axis, but the date slicer triggers two filters, one for First_Scraped <= date slicer [first date] and one for Last_Scraped >= date slicer [last date].
So for instance in the example above, if the user sets the date slicer to 2020-03-01 - 2021-03-01, the line graph will only show the count(ID) for NY-New York, because that's the only State-County combination where First_Scraped is before or equal to 2020-03-01 and Last_Scraped is after or equal to 2021-03-01.
If the user dragged the date slicer's start date to 2021-01-01, the line graph would display count(ID) for both places, because for both, First_Scraped is before or equal to 2021-01-01 and Last_Scraped is after or equal to 2021-03-01.
Hi orionjtaylor ,
First, you need to create a date dimension table if there is no date table in your model. Please note that don't create any relationship between date dimension table and your fact table base on date field. Then create a measure as below, please find the details in the attachment.
Measure = VAR _mindate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR _maxdate = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) RETURN CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[First_Scraped] <= _mindate && 'Table'[Last_Scraped] >= _maxdate ) )Best Regards
4 Replies
Hi orionjtaylor ,
First, you need to create a date dimension table if there is no date table in your model. Please note that don't create any relationship between date dimension table and your fact table base on date field. Then create a measure as below, please find the details in the attachment.
Measure = VAR _mindate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR _maxdate = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) RETURN CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[First_Scraped] <= _mindate && 'Table'[Last_Scraped] >= _maxdate ) )Best Regards
- amitchandak
Super User
orionjtaylor , Assume you have an independent date slicer
Try measure like
measure =
var _max = maxx(allselected(Date), Date[Date])
return
calculate(countrows(Table), filter(Table, Table[First_Scraped] <=Min && Table[Last_Scraped] >= _max ))
Hi amitchandak I get the error "the syntax && is incorrect". Could you explain what this measure does? (Very new Power BI user.) Thank you!
- amitchandak
measure =
var _max = maxx(allselected(Date), Date[Date])
return
calculate(countrows(Table), filter(Table, Table[First_Scraped] <=_max && Table[Last_Scraped] >=
_max ))
based on selected date in slicer , I am trying to filter the rows based on two dates
