Forum Discussion

Julia_Mav's avatar
Julia_Mav
Icon for Helper II rankHelper II
2 years ago
Solved

Unique value for joining

Hi everyone,

I'm having trouble creating a unique key to join two tables in Power BI. I would appreciate it if someone could advise me on how to create this unique key in Power BI with the 'Material' field, using a rule that involves selecting the 'Part Number' with the most recent 'Date Effective'.

The key value with 'Material/ValidFromYear' (25621352100/2020) is not working; see below. 

 

Thanks in advance.

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Julia_Mav ,

    Do you want to get the part number with the most recent effective data for per material? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

    Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date Effective] ),
        FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) )
    )
RETURN
    IF ( 'Table'[Date Effective] = _maxdate, 'Table'[Part number] )

    Best Regards

3 Replies

  • KEAlexander's avatar
    KEAlexander
    Icon for Helper I rankHelper I
    2 years ago

    Hi Julia, when you say "not working" would you please give more details on exactly what is going wrong?

    • Julia_Mav's avatar
      Julia_Mav
      Icon for Helper II rankHelper II
      2 years ago

      Hi KEAlexander, it does not provide the Part number with the most recent Effective date. It gives the value that is in B2, while B3 is needed.  Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Julia_Mav ,

        Do you want to get the part number with the most recent effective data for per material? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

        Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date Effective] ),
        FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) )
    )
RETURN
    IF ( 'Table'[Date Effective] = _maxdate, 'Table'[Part number] )

        Best Regards