Forum Discussion
Filtering on multiple Dates at the same time
Hi Anonymous
Try to build an unrelated date table and build measures to filter your visual by slicer.
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))Relationship:
Measures:
Filter Measure1 = IF(MAX('Call Data'[Creation Date])>=MIN('Date'[Date])&&MAX('Call Data'[Creation Date])<=MAX('Date'[Date]),1,0)Filter Measure2 = IF(MAX('Ticket Data'[Creation Date])>=MIN('Date'[Date])&&MAX('Ticket Data'[Creation Date])<=MAX('Date'[Date]),1,0)Measure1 is for call data table and measure2 is for ticket data table. Build visuals , add measures into filter field in visual and set it to show items when value =1.
Select Year =2021 and Month = 1 and Customer ID =1. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply, unfortunatly I already tried that approach and it does not work because I have already active relations between the Ticket Data and the Call Data on the Customer ID Field.
When I add the relations to the date fields it deactivates one of the relations.
Anonymous in this case, you can use inactive relationship to active relationship in dax. this way you get to keep the existing active relationship between tables and at the same time use inactive relationship in dax measure.
USERELATIONSHIP function (DAX) - DAX | Microsoft Docs
Power BI - Using inactive relationships in a measure. - Hat Full of Data
negi007thank you for your help so far! I tried it but it did not work because I cant use the userelationship measure in the filter pane.
Basically, I need to have 2 values to filter on for all report pages:
- Customer Name
- Year & Month
I dont see any possibilty to do that with userelationship currently.
Hi Anonymous
Try to build an unrelated date table and build measures to filter your visual by slicer.
Date Table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))Relationship:
Measures:
Filter Measure1 = IF(MAX('Call Data'[Creation Date])>=MIN('Date'[Date])&&MAX('Call Data'[Creation Date])<=MAX('Date'[Date]),1,0)Filter Measure2 = IF(MAX('Ticket Data'[Creation Date])>=MIN('Date'[Date])&&MAX('Ticket Data'[Creation Date])<=MAX('Date'[Date]),1,0)Measure1 is for call data table and measure2 is for ticket data table. Build visuals , add measures into filter field in visual and set it to show items when value =1.
Select Year =2021 and Month = 1 and Customer ID =1. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous thanks for the tipp, thats works (also works with filters instead of slicers)