Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]?
Solved! Go to Solution.
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:
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.
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
@Kevin_Conseil , I only have doubt if dates are there it might become Many to Many
Yes it is a many to many relationship. Do you ave any thought about possible negative side effect?
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:
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.
@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
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.
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.
Thanks Alice for your answer, but i do have all dates of sales in my DimDate table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |