Custom Date Filter for two facts

[ssesham]

Dear all, 

I am having a scenario with 2 fact tables and one Date Dimension table that links the 2 fact table (as shown in Data model screen shot).  I am using custom date filter as shown below in 2nd screen shot,  i am using the measure in visual which is having DAX formula to dice and slice the data according to my filter (because of the custom date filter). 

Issue : 

if i choose week_end_date from Fact 1 it shows the results properly for fact 1 measures but not for fact 2 measure, if i choose week_end_date from Fact 2 it works fine for the measures from Fact 2 and Fact 1 measure are not dicing.

If I use the week_end_date from Dim_Week table in visual for x-axis it is not dicing or slicing at all for both measures from fact 1 and fact 2. Beacause week_end_Date col i have used for filter visual is from Dim_Week (so it will have only one value to return from drop down).

Is there is a way to use week_end_date in x-axis that can slice and dice both measures from these 2 facts, based on the filter?

I think i have tried my best to explain the problem, Please advice if any solution for this scenario, last option could be combining 2 fact to 1 fact, but i don't want to go with it.

[miTutorials]

Check out the below tutorial - hope this helps.

Dealing with Multiple Date Fields to bring values from same time period in Power BI | MiTutorials (y...

[ssesham]

@miTutorials  Thank you so much, userelationship in DAX works well in this scenario. But i have a question in my data model, do you want me to drop a relationship from one of the fact to date dimension and create relationship many to many to 2 facts directly?  then only it is working.

[v-tianyich-msft · Accepted solution]

Hi @ssesham ,

It's generally recommended to avoid many-to-many relationships directly between fact tables. Instead, use a bridge table or a common dimension table to manage these relationships.

Create a bridge table that contains unique keys from both fact tables. This bridge table can then be used to establish one-to-many relationships with each fact table. This approach simplifies the model and improves performance

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[ssesham]

That's what i have changed datamodel now with Bridgetable (factlessfact). Much appreciated your reply.