Fliter two tables by multiple (day, month, year) filters

[pal95]

Hello,

To simplify it as much as I can - I have 2 tables in PowerBI:
- Table 1 - Actual Number of Checks

- Table 2 - Planned Number of Checks

I have a chart as below:

Slicers are based on table 1. I created a relationship on 'Week' between tables 1 and 2, and when I change 'Week', it is all working fine. When I change 'Year' or 'Month', it works fine only for table 1, but not for table 2.

So, table 2, week 2 of 2022 shows me data for 2021 as well, unfortunately.

Could you please advise me on how to make slicers work for both tables?

[v-angzheng-msft · Accepted solution]

Hi, @pal95 

Since the slicer month and year are from one of the tables, you cannot filter table 2.

You can summarize the year, month, and week from Table 1 and Table 2 into a new Table 3, then create a relationship between Table 3 and Tables 1 and 2, and use the fields from Table 3 to create the slicer.
Since Table 3 contains only those years, months, and weeks, Table 3 will not have too many rows.

Try to create a calculation table as follows:

T3 =
VAR _t1 =
    SUMMARIZE( 'table1', [year], [month], [week] )
VAR _t2 =
    SUMMARIZE( 'table2', [year], [month], [week] )
RETURN
    UNION( _t1, _t2 )

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[pal95]

Sorry I wasn't clear - creating a 3rd table isn't possible - I have tens of thousands of records and hundreds of columns in each of the tables, and these 2 tables are connected to other tables by DAX'es and various relationships. Making year slicer work fine is literally the last thing I need to do with this whole dashboard, so any big structural change is very unwanted. Thank you

[amitchandak]

@pal95 , You need to create a common date table join with the date of both tables. Add columns like the year, month and week in date table and use those in the slicer and axis

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[pal95]

Hi, I am afraid it isn't possible to create a new table in this case as these 2 tables are designed for totally different events and can't be merged. Is there any other way?

[amitchandak]

@pal95 , I am asking for a third new table, not the merge of two. Is it possible?