Connection between the Date Column in two different data source is not working - Slicer doesn't work

[anili]

Hi,

I have two data from two seperate excel tables:

1. Statistics Sheet:

2. Audit Sheet

Statistics sheet has Month column to indicate the period of reporting. It is always 1st day of the month. In the fields, it is already noted as a date hierarchy as well. I am using a slicer to filter the period in my report view. It works well in all the visuals. 

Similarly, I have Audit Date column in Audit data sheet. Which can be various different dates in a month. 

So, I am trying to achieve my slicer to filter the month period which should then filter the visual that is fed by Audit sheet as well. 

I believe I have to establish a link between the Month column in Statistics sheet to Audit Date column in Audit sheet. When I try to do that, I receive warning: "This relationship has cardinality - Many to Many". It still allows me to establish the relation. However, the slicer filter (Period selection for month) does not apply to visuals that are fed from Audit sheet. 

I have similarly Many to Many type relation in Project Name columns of two sheets. My project name slicer is working perfectly fine. I tried changing the formats of the dates, adding a new column to only list the month name. or make individual dates in audit sheet to be 1st of the month to match the other sheet. Nothing worked. 

I will appreciate if someone can advise on what might be the problem.

Thanks in advance. 

All the best!

[johnt75 · Accepted solution]

You need to create a couple of dimension tables and link them to your fact tables. Firstly, create a proper date table, marked as a date table. Delete the relationships between your fact tables and create one-to-many relationships from your date table to each fact table.

Then create another dimension table for your project names. You can do this in Power Query by combining the columns from each sheet into a new table and then removing duplicates, or you could do it in DAX like

Project Names =
DISTINCT (
    UNION (
        DISTINCT ( 'Audit'[Project Name] ),
        DISTINCT ( 'Statistics'[Project name] )
    )
)

Then create one-to-many relationships from the new dimension table to each fact table.

Use columns from the dimension tables in your visuals, slicers etc and everything should filter correctly.

[anili]

Thank you Johnt75. It worked👌