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.
Hello,
I have two tables in a SSAS Tabular model.
These two tables are related by DimProducts[Order Date ID] to DimDate[DateID]. [Delivery Date ID] also shares an inactive relationship with DimDate[Date ID].
I then connect to SSAS Tabular with Power BI.
I create a simple bar chart with DimDate[Month] on the X Axis and drag DimProducts[delivery date], DimProducts[Order Date] to the chart value section to create a monthly total of orders and deliveries on one chart.
However! The chart only returns a count of deliveries per month where the order was also in that month (e.g. the active relationship). Is this normal behaviour?
I can partially around this by creating a calculated measure in SSAS Tabular, but based on some great input from @MattAllington we can't use 'see records' in Power BI if we use calculated measures from SSAS Tabular (hence including the actual dates [Order Date], [Delivery Date] in the fact table to enable Power BI to do the aggregating so I can 'see records'.
Is there another simple way of handling this? Or will I need to normalise the fact table to store [Order Dates] and [Delivery Dates] into a single column?
Thanks!
Pbix
Hi there, yes this is the normal behaviour.
If you want to have measures based on the [Delivery Date ID] you'll need to specifically specify the inactive relationship is to be used for that measure. So it is possible, just means a bit more DAX in your measurse.
This is a function you can use to take advantage of the inactive relationship.
https://msdn.microsoft.com/en-us/library/hh230952.aspx
Hi Phil,
Thanks for you reply. 🙂
No problem creating a calculated meaure in SSAS to do this - i.e. calculate(counta(),userelationship()) but if I want Power BI to handle aggregation itself (i.e. without me creating the above measure in SSAS Tabular) then I don't think I can specify the specific relationship that Power BI uses to 'automatically' handle aggregation?
I'm just trying to work around the issue where we can't view row detail in a SSAS database where we use calculated measures generated in SSAS...which is really frustrating! 🙂 Is the only way to do this to normalise [delivery date], [order date] into a single column in the fact table so the cube stores 'facts about delivery dates', facts about order dates' in a single field? This would mean creating two rows (or more, if additional significant dates need to be analysed) for every product sold. Is this generally a better approach to design of any cube?
Thanks,
Pbix
Hi @pbix,
From Power BI side, if you don’t want to create measure to calculate deliveries and want to use “See records” feature, you can consider to create another table to contain [Product ID] , [Delivery Date ID] and [Delivery Date] columns, then create active relationship using [Delivery Date ID]column between this new table and DimDate table. This way, create a bar chart by dragging [Order Date] of DimProducts table and [Delivery Date] of bew table to value section.
In addition, about the cube design questions, I would recommend you post the question in the SSAS forum at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices . It is appropriate and more experts will assist you.
Thanks,
Lydia Zhang
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 |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
146 | |
106 | |
104 | |
89 | |
65 |