Forum Discussion
Issue with Multiple date keys and displaying dates on report.
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?
7 Replies
- lbendlin
Super User
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?
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.
- lbendlin
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).
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).
