Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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:
Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn
Measures in Power BI Desktop - Power BI | Microsoft Learn
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.
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?
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:
Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn
Measures in Power BI Desktop - Power BI | Microsoft Learn
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
You can always lean on us. If you still have other issues don't hesitate to post a new question and tag me 🙂
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
115 | |
74 | |
57 | |
47 | |
39 |
User | Count |
---|---|
167 | |
118 | |
61 | |
58 | |
50 |