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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mede
Resolver I
Resolver I

Need to filter Date Dimension based on start date of selected Store from Location Dimension

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.

 

data_model.JPG

- 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?

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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