Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following model setup:
Fact B table is interconnection between A and C, but A and C are completely independent of each other. What this means, is that we do reports on Fact C independent of data in Fact A and we do reports on Fact A independent of data in Fact C. When we do reports on Fact B, Fact A and Fact C are used for filtering, so they become dimensions in that case.
With the current setup, if I report something on Fact A (e.g. an aggregate), and I have Dimension X set as a report level filter, if there is no corresponding row in Fact B for a row in Fact A, the row in Fact A is not included in the calculation. Which is an expected scenario for my model setup, but not for my use case.
Now, I'm thinking that I should import Fact C and Fact A tables twice, and the second time renamed them into Dimensions. Then, the Fact tables should never be interconnected with each other. Is this a good approach? I want to keep Dimension X and Dimension Z as common filter for ALL fact tables reports, so I can use them as global page or report level filters. Use them as slicers as well.
Thanks!
Gorgi.
Solved! Go to Solution.
@Anonymous
In your current relationship, there is an inactive relationship between Project and ProductInstance. You only need to create a ProjectId measure which using the inactive relationship to replace the ‘ProductInstance’[ProjectId] column in the table visual.
I've uploaded the PBIX file here for reference.
Projectid_Measure = CALCULATE ( MAX ( ProductInstance[ProjectId] ), USERELATIONSHIP ( Project[Id], ProductInstance[ProjectId] ) )
Best Regards,
Herbert
Could you post some sample data from your fact tables and dimension X table? You might be able to achieve what you want by editing the cross filtering settings for your relationships.
I cannot share any data or structure, but lets say that Fact C is a customer, Fact A is an instance of a product in a specific Dimension X and Dimension Z is a product information. Fact B would be customer interacting with product instance C. The Fact C contains summary data for the product instance, like turnover.
Fact C (the customer) is unique and lives in a dimension X. So, it also contains summary information for the customer, like total turnover.
@Anonymous you don't have to share real data, just an example so we're working with the same kind of materials as you. https://www.mockaroo.com/
Proud to be a Super User!
Thanks for your help guys. I will try to do that by tomorrow.
Cheers!
here is a one simplified example: https://www.dropbox.com/s/f8xmg0r8pvkxxao/Test.pbix?dl=0
From the diagram above, I'm only missing Dimension Y, which is a time dimension. But, the Facts here are Customer, Product Instance and CustomerProductInstance. A customer can exist in the Customer fact, without corresponding CustomerProductInstance.
Looking forward to your help!
Gorgi.
@Anonymous
In your current relationship, there is an inactive relationship between Project and ProductInstance. You only need to create a ProjectId measure which using the inactive relationship to replace the ‘ProductInstance’[ProjectId] column in the table visual.
I've uploaded the PBIX file here for reference.
Projectid_Measure = CALCULATE ( MAX ( ProductInstance[ProjectId] ), USERELATIONSHIP ( Project[Id], ProductInstance[ProjectId] ) )
Best Regards,
Herbert
Thanks @v-haibl-msft this helps. It even works when I add a demographic slicer, so it shows products that have been interacted with specific demographic.
One issue is that I have to bring the Projectid_Measure in the reports, so the relationship is included.
But is this approach recommended? One solution suggested here: (scroll a bit up and see the two bullet points) is to "Bring in a table twice (with a different name the second time) to eliminate loops. This makes the pattern of relationships like a star schema. With a star schema all of the relationships can be set to Both.". But, this approach is more complex and doesn't work good with page level filters (and slicers).
I will try this solution to our specific model and see how it works.
Cheers.
@Anonymous
You can try both of these two most common approaches to see which is more suitable for you.
Best Regards,
Herbert
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |