topic Re: Create one slicer / measure from dates in two tables? in DAX Commands and Tips

Create one slicer / measure from dates in two tables?

carlenb — Wed, 20 Sep 2023 12:25:56 GMT

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 🙂

Re: Create one slicer / measure from dates in two tables?

ERD — Wed, 20 Sep 2023 12:04:23 GMT

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

Re: Create one slicer / measure from dates in two tables?

carlenb — Wed, 20 Sep 2023 12:24:59 GMT

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?

Re: Create one slicer / measure from dates in two tables?

JoeBarry — Wed, 20 Sep 2023 12:35:46 GMT

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

Re: Create one slicer / measure from dates in two tables?

carlenb — Wed, 20 Sep 2023 14:34:44 GMT

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?

Re: Create one slicer / measure from dates in two tables?

JoeBarry — Thu, 21 Sep 2023 07:10:06 GMT

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.