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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors