Create one slicer / measure from dates in two tables?

[carlenb]

Hi,

I have two tables. I want to create ONE slicer to filter out the year for both "Year" and "Date order". The difference in the data is that table 1 is 2023 data and table 2 data is past data. See below. 

Edit: The data is table 1 and table 2 is not the same. There will be columns and data not existing in the other table. 

How can I create a slicer/measure for this in DAX? I tried to create a relationship in model view but that doesn't work. 

Thanks for any help on this 🙂 

[JoeBarry · Accepted solution]

Hi @carlenb 

You should always have a date table in your data model. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Any table that has a relationship with the date table can be compared to each other at a date level. 

When you create your date table, create a one to many relationship between the date column in the date table and the date column in your two existing tables. There shouldn't be any relationship between the exsiting two tables. 

Any visuals or slicers you have that require a date, then please use the columns from the date table instead of the existing tables.

Thanks

Joe

If this post helps, then please Accept it as the solution

[JoeBarry · Accepted solution]

Hi @carlenb 

You should always have a date table in your data model. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Any table that has a relationship with the date table can be compared to each other at a date level. 

When you create your date table, create a one to many relationship between the date column in the date table and the date column in your two existing tables. There shouldn't be any relationship between the exsiting two tables. 

Any visuals or slicers you have that require a date, then please use the columns from the date table instead of the existing tables.

Thanks

Joe

If this post helps, then please Accept it as the solution

[carlenb]

@JoeBarry thank you, I think I managed to get it to work 🙂 

Now with my dates up and running, let's say I want to connect the suppliers in my two tables so that I can calculate quantity, volumes etc. Some suppliers will exist in both, some will exist in only one. What would be the best practice? Do I simply create a many to many relationship between "Suppliers" in both tables? 

[JoeBarry · Accepted solution]

You will need to create a supplier dimension table. This will have all of your suppliers and you create the same realationship as the date table. 

You should read this article https://learn.microsoft.com/en-us/power-bi/guidance/star-schema It will help you develop a good data model, which will allow you to make comparisons acroos multiple tables. 

[carlenb]

Thanks @ERD . Good point, but the data is not the same. There will be data and columns not existing in one and another. 

Any ideas? 

[ERD]

If the tables are the same, but with different periods, I would union them to one table in Power Query first.