topic Re: Issue with Multiple date keys and displaying dates on report. in DAX Commands and Tips

Issue with Multiple date keys and displaying dates on report.

Cosmac — Tue, 06 Aug 2024 11:03:18 GMT

Hello, i am working on a SSAS Tabular model to support Power BI report development. Our main fact table has multiple dates (Order Date, Paid Date, Shipped Date) with the Order Date being the active relationship to the date dimension. One of our reports requires a table that has the following columns (amoung others) Date and Amount by Paid Date. I understand how to build the measure "Amount by Paid Date" using USERELATIONSHIP to active the relationship between my fact.paid_date_key and the dim.date_key.

Other than fully duplicating my date dimension to have a "Paid Date Dimension" and use that inactive relationship. how can i include "Paid Date" in my report table?

Re: Issue with Multiple date keys and displaying dates on report.

lbendlin — Tue, 06 Aug 2024 21:01:36 GMT

Other than fully duplicating my date dimension to have a "Paid Date Dimension" and use that inactive relationship. how can i include "Paid Date" in my report table?

You lost me on that part. Do you need to model the entirety of the relationships in your cube or can you allow the report users to create their own connections?

Re: Issue with Multiple date keys and displaying dates on report.

Cosmac — Thu, 08 Aug 2024 13:52:13 GMT

This report and the majority of them are built using direct connect, so the SSAS Tabular need to be fully modeled out. I believe in that setup, the best that the report developer can do is to create their own measures, but i could be mistaken.

Dim.Date	Relationship	fact.Sales
Date_Key	Active	Order_Date_Key
Date_Key	Inactive	Paid_Date_Key

If i wanted to do a report that looked like the following:

Order Date	Paid Date	Order Amount	Discount Amount	Paid Amount
2024-01-01	2024-01-02	$100.00	$20.00	$80.00
2024-01-01	2024-01-03	$200.00	$0.00	$200.00

So Order Amount is just a SUM, same with Discount Amount. For Paid Amount the DAX looks something like

CALCULATE ( SUM('Fact.Sales'[Paid_Amount]), USERELATIONSHIP ('Fact.Sales'[Paid_Date_Key], 'dim.date'[Date_Key]) )

The issue ive been having is around the "Paid Date" column, how can i display that effectivly in a measure or what other options should i be exploring?

I hope this example is clearer, this is just a rough scenario so it might not be 100% but it should paint a picture.

Re: Issue with Multiple date keys and displaying dates on report.

lbendlin — Thu, 08 Aug 2024 14:18:10 GMT

are built using direct connect,

That's not a thing. Either you have Live Connection (no option to edit) or Direct Query (with option to edit).

Re: Issue with Multiple date keys and displaying dates on report.

Cosmac — Thu, 08 Aug 2024 14:25:13 GMT

Sorry for using incorrect terminology. In power BI i see two options, "Import" and "Connect Live".

The scenario i am talking about is reports build using Live Connection (no option to edit).

Re: Issue with Multiple date keys and displaying dates on report.

lbendlin — Thu, 08 Aug 2024 18:01:00 GMT

Does it have to be SSAS Tabular or could it instead be implemented as a Power BI Semantic Model?

Re: Issue with Multiple date keys and displaying dates on report.

Cosmac — Mon, 12 Aug 2024 13:14:40 GMT

Currently it needs to be a SSAS Tabular model.

Re: Issue with Multiple date keys and displaying dates on report.

lbendlin — Mon, 12 Aug 2024 13:28:41 GMT

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .