Two tables have date columns but date slicers from either table will not drive both

[athogan]

I have an iVent table and a Discharge table in a power bi dashboard. They are linked on Department and they both have date columns that are in the same format. When I add a date slicer from the Discharge table it only filters data from that table and vice versa. I have tried adding a seperate date table but if I join on date the departments do not sync. How do I fix this issue??

[DataNinja777 · Accepted solution]

Hi @athogan ,

Your data model has to be a star schema, meaning, that there should be dimension tables and fact tables in your data model. Currently, your data model has many to many relationship between the fact tables, with the department key.  Instead, it is recommended that you do not allow many to many relationship in your data model.  First, you can create a dimension table of department which should be linked to both 'Discharges' and 'iVents' fact tables where Department dimension table is one side of the relationship and 'Discharges' and 'iVents' fact tables are many sides of the relationship.  Similarly, you can create a calendar dimension table and create relationship with both 'Discharges' and 'iVents' fact tables's date fields.  Once you sorted out your data model structure in the standard star schema way, your dax and filtering will be much easier.  

Best regards,

[v-junyant-msft · Accepted solution]

Hi @athogan ,

@DataNinja777 Thanks for your concern about this case!

And @athogan have you solved your problem? If not, and if I understand correctly, you want to filter the Discharge table by date and at the same time the table iVent will show the data that is within the filter date range and the department is the same as the table Discharge, right?
Here is my sample data:

And here is the relationship:

Use this DAX to create a measure:

Measure = 
IF(
    MAX('iVent'[Date]) <= MAX('Calendar'[Date]) && MAX('iVent'[Date]) >= MIN('Calendar'[Date]) && MAX('iVent'[Department]) IN VALUES(Discharge[DepartmentName]),
    1,
    0
)

Follow the diagram below to set up:

And the final output is as below:

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

[DataNinja777 · Accepted solution]

Hi @athogan ,

Your data model has to be a star schema, meaning, that there should be dimension tables and fact tables in your data model. Currently, your data model has many to many relationship between the fact tables, with the department key.  Instead, it is recommended that you do not allow many to many relationship in your data model.  First, you can create a dimension table of department which should be linked to both 'Discharges' and 'iVents' fact tables where Department dimension table is one side of the relationship and 'Discharges' and 'iVents' fact tables are many sides of the relationship.  Similarly, you can create a calendar dimension table and create relationship with both 'Discharges' and 'iVents' fact tables's date fields.  Once you sorted out your data model structure in the standard star schema way, your dax and filtering will be much easier.  

Best regards,