Filter two tables with one slicer

[Anonymous]

Good day

This might be an easy answer for some. I have two tables, 'Exports' and 'Imports'.

In the 'Export' table I have a field 'Export Date' and in the 'Import' table I have an 'Import Date' field.

I have a date dimension table and I want to create a relationship from each table to the date table so that I can have one slicer that filters on both tables.

I have created the relationship, but I cannot have multiple active relationships in PowerBI.

I've watched videos and read multiple blogs, but they all refer to one date dimension table to two date columns in one table, then they use the USERELATIONSHIP function. This does not work.

What would be the best solution to this? Any advice would be greatly appreciated.

Thanks

[V-lianl-msft]

Hi @Anonymous ,

What fields are the two original tables related to? Can you show a screenshot?

If your export and import tables are one to many based on the date field, you can use the date of one direction table as slicer.

If your export and import tables are many to many based on the date field, you can create a one to many relationship between the date table and the date fields of the other two tables, and delete the many to many relationship between the two tables you created earlier.

If two tables are related based on other fields, you can create a measure and apply it to the visual level filter, and the date table does not need to create a relationship with the other two tables.

Measure = IF(MAX('export'[Export Date])in VALUES('Table'[Date]),1,0)
Measure 2 = IF(MAX('Import'[Import Date])in VALUES('Table'[Date]),1,0)

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

[amitchandak · Accepted solution]

@Anonymous , You said you have two tables. Both Tables can have one Active relation with Date Table.  Userelation is required when you more than one active in one table

refer this when you more than in a table:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

In this one you should able to more than one table joined to a common date table: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[lucasminikoski]

Probably your slicer is using dates from any fact table and not the "Calendar Table". 

And, if you need more than one relationship in each fact table, you cannot slice the both dates' columns of EACH fact table.

[lucasminikoski]

If it doesn't works, you're making it wrong.