Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ssesham
Helper I
Helper I

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?

 

 

ssesham_0-1728865321289.png

 

ssesham_2-1728866021281.png

 

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.

1 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.

View solution in original post

4 REPLIES 4
miTutorials
Super User
Super User

@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 Chang

 

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

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.