Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
judycheung
Frequent Visitor

DAX help with many to many relationship table with dates

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.

 

 

1 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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

5 REPLIES 5
AmiraBedh
Resident Rockstar
Resident Rockstar

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.

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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:

  • 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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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 🙂


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.