Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering on multiple Dates at the same time

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

  • Anonymous's avatar
    Anonymous
    5 years ago

    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 Zhou

     

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

6 Replies

  • negi007's avatar
    negi007
    Community Champion

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