cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbix
Helper III
Helper III

Working with multiple dates on a single axis

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

3 REPLIES 3
Phil_Seamark
Microsoft
Microsoft

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.