Working with multiple dates on a single axis

[pbix]

Hello, 

I have two tables in a SSAS Tabular model.

• DimDate ([Date ID]. [Month], [Date])
• DimProducts ([Product ID], [Order Date ID], [Delivery Date ID], [Order Date], [Delivey Date])

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

[Phil_Seamark]

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

[pbix]

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

[v-yuezhe-msft]

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