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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Mkrishna
Helper III
Helper III

Data Modeling and DAX, linking date dimension and other dimesion

Hi Everyone, I hope you are doing well and thank you reading my queries. 

I have three tables

1. Fact Table : It contains customer, customer name, sales , date , brand column. I have extracted month and year from the date column. The table basically shows the visit date of store and which product were there at the time of visit and the sales

 

2. Customer Table: This table contain the total list of store in month wise with customer number, customer name, sales manager, sales rep and the date. The date column show which month and year  when the list was downloaded. The number of store can change month wise so there is time stamp.

 

3. Date table.

 

I am new to PBI and this is the first time I am modeling a table where there is date in dimension table  Customer Table. 

 

 

My goal.

I want to execute followint things via this project.

1. When the date is selected in slicer (let say in this example the user select Dec -23), it should filter the Customer table to Dec -23.

2. Now see how many customer present in Dec -23 in the customer table visited the store in last 3 months. i.e do the intercept of  Dec -23 Customer Table's customer with  (Oct, Nov, Dec ) data from Fact Table. The intercept count gives us the total customer for Dec- 23.

 Similary, if the selected date in date slicer is Nov -23 ,  it should filter the Customer table to Nov - 23. Now see how many customer present in Nov -23 in the customer table visited the store in last 3 months. i.e do the intercept of  Nov -23 Customer Table's customer with  (Sep, Oct, Nov ) data from Fact Table. The distinct count of the numbe rof intercept customer gives us the total customer for DecNov-23

 

3. Now from the list of Dec -23 customer which we found in step 2, see what is the latest visit from the Fact table and see if the latest visit has brand Coca. If yes give 1, else 0. The sum all 1. This give the total customer.

 

 

I am able to make a simple data model when there is no date dimension in customer table but now since there is date dimension in customer table, I am unable to model. should it be model as two fact table data model. When I link date table and fact table and similary link fact table and customer table. I am unable to shift the date to backward. i.e when selected month is Dec- 23, I am unable to go to Dec, nov and oct. In my previous model i,e simple one I have use another date dimension and connected it with date dimesion and made the relationship inactive.

 

Here is the PBIX file https://drive.google.com/file/d/1ICZZfFk_Grrdw-63xuSCNQ0h01LmWVIj/view?usp=sharing

 

 

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@Mkrishna 

 

option 1 

link date 1 to many to fact 

link customer 1 to many to fact 

 

link date to customer via an inactive relationship .

 

now whenver you need to calcualte anything base on the linkage between customer and date,  you should use in your measure : 
calculate ( sum( table_name[col_name] ) ,  userelationship ( customer[date] , date_table[date]) )

option2 : 
now basically, you can create w new date table that links to customer  and the original ddate links to fact table . 

since , the date in customer for the business is different than the date in fact table for the business. 

 

but now you have 2 dates table, that you can work on . 

 

 

now i would recommend using the first approach . 

 

however sometimes you need to creates 2 date tables. it all depends on your requirements / 

 

 

 

 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

 

 

 

View solution in original post

1 REPLY 1
Daniel29195
Super User
Super User

@Mkrishna 

 

option 1 

link date 1 to many to fact 

link customer 1 to many to fact 

 

link date to customer via an inactive relationship .

 

now whenver you need to calcualte anything base on the linkage between customer and date,  you should use in your measure : 
calculate ( sum( table_name[col_name] ) ,  userelationship ( customer[date] , date_table[date]) )

option2 : 
now basically, you can create w new date table that links to customer  and the original ddate links to fact table . 

since , the date in customer for the business is different than the date in fact table for the business. 

 

but now you have 2 dates table, that you can work on . 

 

 

now i would recommend using the first approach . 

 

however sometimes you need to creates 2 date tables. it all depends on your requirements / 

 

 

 

 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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