Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I think this is a data modelling challenge.
I have multiple fact tables connected to a Dim Date and Dim Location (5 retail stores) with a usual Many to one single direction relationship (star schema).
The challenge is the following:
- I have identified 3 test stores and 2 control stores in Dim Location with in a column called Test/Control.
- For each Test store that i select (forced single selection) in the dashboard slicer (or filter), i would like to show sales of the Test store vs 2 Control stores
- BUT... each individual test store has a different start date for the test, therefore i need the Dim Date to filter based on the start date of the test of the selected Test Stores.
I've been trying to use a bridge table for this but no luck due to at least one of the connection is forced to be inactive due to ambiguity.
- You will notice in the data model that, i decided to create a Dim Test Name where there are three unique test names.
- I created a Dim Location bridge which is similar to Dim Location but station ids for control sites are repeated for different test names.
- Stg Period is a bridge table that has 3 copies of each date for each Test Name. Period is where it says Pre Test or Post Test based on station id and date.
Is this not the correct approach? any better ides?
Hi @mede,
It is hard to clarify your scenario from screenshots, can you please provide the sample pbix file to test? I think it will be help for us.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |