Filtering on multiple Dates at the same time

[duufkees]

Hi,

I have a problem and though of different solutions but none of them worked.

I have this kind of table structure:

Ticket Data

Creation Date	Customer ID	...
1.1.2021 14:30	1
2.1.2021 15:00	2
3.1.2021 16:00	3

Call Data

Creation Date	Customer ID	...
1.1.2021 14:00	1
4.1.2021 14:30	1
5.1.2021 50:00	2

As you can see, there is a relation to a customers table.

My goal:
I want my customers to select one year and one month from the filters pane and all the data from the different reports pages get filtered based on the selected month.

What I tried:

- Use a relation between the dates: this wont work because I already have a relation on the Customer

- Use another global dates table: only use measures to display the values and filter in the measure for the selected date. This wont work because I cant use a measure for the date axis in the visuals. Because on some pages I need to display the last 6 Months based on the choosen one.

- Use synced and grouped slicers: I create a year and month slicer in the first Page of the report and sync the selected value to the others slicers. This actually works in the first place, but after publishing it to Power BI Desktop this is not working correctly (also I need to use filter for embedding the reports later, so doing it with filters would be great, if possible),

- Use a calculated column with a calculation to check if the data row should be displayed or not and filter on that value. This wont work because the current filter context is not passed to the calculated column, so it never notifies which date was actually selected.

Of cause in this simple version one could say, come on, let users pick 2 dates. Well I have a lot more such tables from other sources and its all the same problem, there is a customer relation already.

Does somebody have any idea on how to solve this problem?

Thanks in advance, I'm struggling with this a long time now..

[negi007]

@duufkees  i would suggest you to create a seperate date table and then link all date fields in other tables to the main date table. use only date field from the main date table for filtering and calculation. this should resolve your issue as your main date table will have all the date values which are there in the other tables.

below is the simplest way to create a global or seperate date table.

Calendar = CALENDARAUTO()

let me know if this helps you.

[duufkees]

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.

[negi007]

@duufkees 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

[duufkees]

@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.

[v-rzhou-msft · Accepted solution]

Hi @duufkees 

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[duufkees]

Hi @v-rzhou-msft thanks for the tipp, thats works (also works with filters instead of slicers)