DAX help with many to many relationship table with dates

[judycheung]

Can anyone help with this please?  I need a DAX function to return a category from many-to-many relationship tables.

Table1 is a transaction table with a product ID field and transaction date field 
eg ID = 1, Date = 01/02/2023
     ID = 2, Date = 01/03/2023
     ID = 1, Date = 01/07/2023 

Table2 is a lookup table linked to Table1 with a many to many relationship on the product ID field and a date range
ID = 1, Category = A, FromDate = 01/01/2023, ToDate = 31/05/2023
ID = 1, Category = B, FromDate = 01/06/2023, ToDate = null  (current active record)

For a given transaction record from Table1, I need to retrieve the right Category that was relevant for the same ID and TransactionDate between the FromDate/ToDate from Table 2.
Eg. if the TransactionDate is in Feb 2023, then it returns Category A; if the TransactionDate is Aug 2023, it returns Category B

I've tried related() and relatedtable() functions and filter without success.

[AmiraBedh]

You will need  `RELATEDTABLE`, `FILTER`, `MAX` (for handling nulls in `ToDate`), and `RELATED` (to pull the desired column from the filtered table).

CategoryLookup =
VAR CurrentTransactionDate = MAX( Table1[Date] )
VAR CurrentProductID = MAX( Table1[ID] )

VAR FilteredCategoryTable =
FILTER(
RELATEDTABLE( Table2 ),
Table2[ID] = CurrentProductID &&
Table2[FromDate] <= CurrentTransactionDate &&
( ISBLANK( Table2[ToDate] ) || Table2[ToDate] >= CurrentTransactionDate )
)

RETURN
IF( NOT( ISEMPTY( FilteredCategoryTable ) ),
RELATED( Table2[Category] ),
BLANK()
)

You will need to retrieve the current transaction date and product ID from `Table1` and then filter the related entries in `Table2` to those that match the current product ID and where the transaction date falls within the range of `FromDate` and `ToDate`.
Then for the `Category` you will retrieve the data from the filtered related table, or returns BLANK if there's no match.

[judycheung]

Thanks, I will try this tomorrow.  Could you please explain the reason for the MAX() function.  I need to loop through each transaction to retrieve the right category for that ID and TranDate but is MAX just finding the highest one?  Also, I'm new to PowerBi and DAX so could you advise on whether I need to add a Calculated column or Measure?

[AmiraBedh · Accepted solution]

If you want to create a new column in Table1 containing each row's category value, then you need a calculated column. It operates row-by-row, so there's no need to aggregate the values.

CategoryLookup = 
VAR CurrentTransactionDate = Table1[Date]
VAR CurrentProductID = Table1[ID]

VAR RelatedCategory = 
    CALCULATE(
        VALUES(Table2[Category]),
        FILTER(
            Table2,
            Table2[ID] = CurrentProductID &&
            Table2[FromDate] <= CurrentTransactionDate &&
            (ISBLANK(Table2[ToDate]) || Table2[ToDate] >= CurrentTransactionDate)
        )
    )

RETURN
    IF(
        NOT(ISBLANK(RelatedCategory)),
        RelatedCategory,
        BLANK()
    )

Aalways remember:

• Calculated Columns operate row-by-row and add to the size of your data model since they store results for each row.
• Measures aggregate data based on the context in which they are used in reports (e.g., summing up values for a specific filter context). They are more dynamic and don't increase your model size in the same way columns do.

Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn

Measures in Power BI Desktop - Power BI | Microsoft Learn

[judycheung]

Hi Amira
That's great thanks.  I have a further complication that the ProductID is null because it's legacy data where the ID number is not populated.  I'm pretty sure the calculation you have given me will work perfectly and thanks for the explanation on measures/columns.

Thanks
Judy

[AmiraBedh]

You can always lean on us. If you still have other issues don't hesitate to post a new question and tag me 🙂