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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
marksaba
Helper II
Helper II

All Results for one table to appear when another table is filtered

I have two data sets with a Unique ID that is Product ID - Customer ID - Date. One data set is a SQL import. The other data set is a UNION table of several forecast files. The goal is to be able to compare Actual Sales (SQL Import) to Forecast and be able to slice by forecast. 

 

I have created the necessary relationships, a Many to Many Cardinality with Both as the Cross Filter direction. Below shows the correct value for Forecast Revenue (sum of the two forecast files) and Invoice Sales (SQL Import).

 

Screenshot 2023-09-27 123904.png

 

However, when I filter to one of the forecast files, then Invoice Sales reduces as well, as you can see below. I believe this is because, when filtering the Forecast Date, I only then see Invoice Sales values of the Product ID - Customer ID - Date that also appear in the Forecast Revenue file from 1/3/23. I want to always see the Invoice Sales total, regardless of the Forecast Date. Meaning, the difference of $383.7M in the above screenshot to the $296.3M in the below screenshot ($87.4M) are Invoice Sales that were not forecasted for, at least at the same Product ID-Customer ID-Date. My goal is to be able to show all of the Invoice Sales and Forecast. Meaning, when I filter to 1/3/23 for Forecast, I want to see everything that either had Forecast on 1/3/23 and everything that had Invoice Sales. So if a Product ID-Customer ID-Date did not have a Forecast but did have Invoice Sales, I still want that to be factored in but just show $0 as the Forecast.

 Screenshot 2023-09-27 123904.png

 

Can anyone help?

 

Thanks

1 ACCEPTED SOLUTION
marksaba
Helper II
Helper II

Resolved by adjusting the Cardinality of the relationship!

View solution in original post

3 REPLIES 3
audreygerred
Super User
Super User

Hello, rather than joining your two fact tables directly together, I would suggest a Star-Schema model. Your fact tables would be the Sales and the Forecast and you would get the facts to work together by utilizing dimension tables (i.e. Product, Customer, Date). By switching to a Star-Schema your forecast table won't filter the Sales table. You can utilize the Forecast Date from your Forecast fact table to choose which forecast you want to show, or you can add in a second date table so that you can filter both the sales and forecast independantly.

http://powerbiwithme.com/2023/07/30/the-star-schema-edition/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





marksaba
Helper II
Helper II

Resolved by adjusting the Cardinality of the relationship!

calerof
Impactful Individual
Impactful Individual

@marksaba,

Do you have a Date table in your model?

F

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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