Forum Discussion
Custom Date Filter for two facts
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.
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Check out the below tutorial - hope this helps.
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.
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
