cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

date column from dim date table filtering 2 fact tables on 2 different columns

Hi everyone,

 

Tables:
'DimDate' -> Dimension table; date column being the key reference to filter fact tables

'SalesMonthly'-> Fact table with sales numbers on a monthly level

'SalesDaily'-> Calculated fact table with sales numbers on a daily level (cross join between 'DimDate' & 'SalesMonthly')

 

Data model connection:

'DimDate'[Date] -> 'SalesMonthly'[year_month]: dimdate is connected to sales monthly table on date = year_month

'DimDate'[Date] -> 'Salesdaily[date]: dimdate is conneted to sales daily table on date=date

 

Issue:

On my daily table appeare only the sales from the first day of the month (year_month).

 

Troubleshoot:

When i remove the connection in the data model between 'DimDate'[Date] -> 'SalesMonthly'[year_month] then i get the correct sales. 

 

Any idea how to solve the issue on my daily table while keeping the connection between 'DimDate'[Date] -> 'SalesMonthly'[year_month]?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Hi, @Kevin_Conseil 

 

Generally, we don't recommend relating two fact-type tables directly using many-to-many cardinality. The main reason is because the model won't provide flexibility in the ways you report visuals filter or group.

 

I'd like to suggest you refer the following guidance:

  • Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
  • Add a bridging table to store associated entities
  • Create one-to-many relationships between the three tables
  • Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
  • When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
  • Hide the bridging table (unless it contains additional columns or measures required for reporting)
  • Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)

 

For further information, you may refer Many-to-many relationship guidance .

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Post Partisan
Post Partisan

Re: date column from dim date table filtering 2 fact tables on 2 different columns

I think I encountered something similar. Is DimDate a calculated table? If yes, you need to expand the period in there to make sure you 'catch' every Sales date in between its start and end date.

E.g. if you have Sales from 2018 to 2020, DimDate needs to start at least from 01/01/2018.

Super User IX
Super User IX

Re: date column from dim date table filtering 2 fact tables on 2 different columns

@Kevin_Conseil , In the monthly table you can create a month start date and join with Date of date table

 

Create a new column in sales Monthly -- Assume format is YYYY-MM (2020-02)

 

Date = year(left([Month-year],4),right([Month-year],2),1)

 

In case it is Jan-2020 or Jan-20

Date = "1-" & [Month-year] // You have make this a date column



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Thanks Alice for your answer, but i do have all dates of sales in my DimDate table.

Highlighted
Helper I
Helper I

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Hey Amit,

 

the column "year_month" in my Sales Monthly table is the "start of the month" column you are describing. And I did link it to the date column in the date table. The issue is that it is also filtering out my daily table.

Highlighted
Helper I
Helper I

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Solution:

I just created a "year_month" column in my 'DimDate' table and link it to "year_month" in the 'SalesMonthly' table.

 

Seems like working 🙂 

Still interesting in getting another/better proposition if any 

Highlighted
Super User IX
Super User IX

Re: date column from dim date table filtering 2 fact tables on 2 different columns

@Kevin_Conseil , I only have doubt if dates are there it might become Many to Many



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Yes it is a many to many relationship. Do you ave any thought about possible negative side effect?

Highlighted
Community Support
Community Support

Re: date column from dim date table filtering 2 fact tables on 2 different columns

Hi, @Kevin_Conseil 

 

Generally, we don't recommend relating two fact-type tables directly using many-to-many cardinality. The main reason is because the model won't provide flexibility in the ways you report visuals filter or group.

 

I'd like to suggest you refer the following guidance:

  • Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
  • Add a bridging table to store associated entities
  • Create one-to-many relationships between the three tables
  • Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
  • When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
  • Hide the bridging table (unless it contains additional columns or measures required for reporting)
  • Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)

 

For further information, you may refer Many-to-many relationship guidance .

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors