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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Solution Sage
Solution Sage

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/

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.