combine two date columns - sort of Role Playing Dimensions

Question

I have a scenario in Power BI:

I have a simple Transaction Table, with Order and Delivery years in two columns, with an Amount column.

I do not care about the ProductId column for my visuals; the only columns I am intersted are the Order and Delivery years, and Amount.

I am able to get correct values with a single measure, but in two different visuals.

AmountTotal = SUM(TransactionTable[Amount])

When I combine the above two visuals together, I get the following, which is NOT desired:

However, I need a different visual, in which I combine both the datasets, and develop two measures, and a new filter context clubbing the data from the Order Year and Delivery Year.

I am using Power BI embedded in a SaaS application, that does NOT allow me to add new tables, or relationships (obviously).

All I can do is, just add Calculated Columns and Measures in the DAX language on a single existing table (TransactionTable).

I thought of USERELATIONSHIP, am yet to try though; remember, I cannot add a Date dimension table or any Calculated Table to the model. This is a typical scenario of Role Playing dimension in SQL; generally handeled in SSAS-Tabular or Multidimensional Models.

See the .pbix file attached.

Power BI file.

Sam Nseir · Accepted Answer · 2025-04-05 03:14:55Z

0

This isn't possible with the given restrictions. You will need a singular Dimension to achieve this. If your model has any other tables that has all the years then it is possible.

For example, if your model has a Date table with a year column, then your two measures could look like:

Order Amount Total = 
  SUMX(
    DISTINCT(DimDate[Year]),
    var thisYear = CALCULATE( MAX(DimDate[Year]) )
    return CALCULATE( SUM(TransactionTable[Amount]), TransactionTable[Order Year] = thisYear )
  )

Delivery Amount Total = 
  SUMX(
    DISTINCT(DimDate[Year]),
    var thisYear = CALCULATE( MAX(DimDate[Year]) )
    return CALCULATE( SUM(TransactionTable[Amount]), TransactionTable[Delivery Year] = thisYear )
  )

You would then use the DimDate[Year] dimension column in your visual with these two measures.

edited Apr 5 at 3:14

answered Apr 5 at 1:30

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3812887 Apr 6 at 0:15

ok thanks for your suggestion;

Collectives™ on Stack Overflow

combine two date columns - sort of Role Playing Dimensions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related