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
bibhu250
Frequent Visitor

Data Modelling Help

Hi Folks,

 

I have 2 tables that contain transactional data, and want to show measures filtered using a slicer of dates(year - month). I implemented a Date Dim table and the values from this table populate the slicer. Based upon user selection, the selected dates are passed to the transaction table (SP_Delivery_Detail) and expected information is retrived correctly. The relation between Date & SP_Delivery_Detail is Delivery Date ---> Delivery_Date.

 

My second transactional table(L360_Survey) is related to the first (SP_Delivery_Detail) via the relation(active) : CSC_Code. 

 

The L360_Survey has NPS data and hence doesn't have entries for a contigous set of dates. When I created a relation(non active) between the Date & L360_Survey , and filtered using slicer, Selected slicer(time period) information is coming correctly, but any measures containing SAMEPERIODLASTYEAR is not coming correctly. Also, if this relation is created, then a circular relationship exist between all 3 tables.

 

To fix this, I created another Date dim table (Date 1), and used values from that table to populate slicer, then all information is coming correctly. My measure is Sum(Sales) and Sum(Sales) SAMEPERIODLASTYEAR , so if I select 2019 Dec, I get Sales of December of 2019 and 2018.

 

My question: In future, I need to bring more transactional tables which I am sure will be related to SP_Delivery_Detail. Will I then have to keep addding separate Date Dimension tables ? This doesnt seem scalable enough, wanted to know where I am going wrong. There has to be a better way than this.

 

Power BI Model.JPG

 

 

 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

Create only one date table that is marked as the date in date view.

Join it with L360_survey and SP_delivery. SP delivery join should be inactive and should be used only ren required formula's

 

Refer example of userelation : HR-Analytics-Active-Employee-Hire-and-Termination-trend

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

View solution in original post

@amitchandak 

 

I did try what you suggested but for some reason it didn't work. But, I was able to resolve this issue another way using a single date dim as you suggested with a bridge table.

 

I built a bridge table of unique key values between delivery and survey table. Also, 2 active relationships from the same date table fixed my issue.

 

Thanks much.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Create only one date table that is marked as the date in date view.

Join it with L360_survey and SP_delivery. SP delivery join should be inactive and should be used only ren required formula's

 

Refer example of userelation : HR-Analytics-Active-Employee-Hire-and-Termination-trend

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

@amitchandak 

 

I did try what you suggested but for some reason it didn't work. But, I was able to resolve this issue another way using a single date dim as you suggested with a bridge table.

 

I built a bridge table of unique key values between delivery and survey table. Also, 2 active relationships from the same date table fixed my issue.

 

Thanks much.

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.