Calculated column to lookup value filtering by the latest date

DAX DAX Calculations
1

Hi everyone,

I am using Power Pivot and I have two tables loaded to the data model that has no relationship because they have multiple values.

How to create a calculated column in table 2 and return the color from table 1 filtering by the latest date?

image

Thank you in advance.
calculated_column.xlsx (147.1 KB)

2

Hello @cesartozzi,

Thank You for posting your query onto the Forum.

You can add a column directly via Query Editor. Once both the tables are loaded. Paste the below provided query inside the Advanced Editor for Table2 and a column for “Color” will be added.

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"product"}, Table1, {"product"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"date", "color"}, {"date", "color"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Custom", each List.Max( #"Expanded Table1"[date] )),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [date] = [Custom] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom.1] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"date", "Custom", "Custom.1"})
in
    #"Removed Columns"

Final Results
Final Results780Ă—182 5.82 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Lookup Value By Latest Date - Harsh.pbix (52.9 KB)

3

Hi @Harsh , thank you for your response.
The solution worked, however, it takes a long time to load the data.
On my real dataset, I have some hundred thousand rows and in these cases, I usually opt to use relationships in the data model instead of merging on power query.
That is why I would rather use DAX calculated column. Do you know if I could do it?
Thank you again.

4

Hello @cesartozzi,

Here’s the DAX that you can write for creating a calculated column in “Table2” -

Color = 
VAR _Max_Date = 
CALCULATE( MAX( Table1[Date] ) ,
    ALLEXCEPT( Table1 , 
        Table1[product] ) )


VAR _Results = 
CALCULATE(
    CALCULATE( MAX( Table1[color] ) ,
        FILTER( Table1 ,
            Table1[Date] = _Max_Date ) ,
    TREATAS( VALUES ( Table3[product] ), Table1[product] ) ) )

RETURN
_Results

Final Results

I’m also attaching the PBIX file for the reference purposes.

Thanks and Warm Regards,
Harsh

Lookup Value By Latest Date - Harsh v2.pbix (45.8 KB)

1 Like
5

Hi @Harsh , thank you again for your support.
I am already feeling bad to ask you for more help, but I am working on Power Pivot on Excel, and TREATAS is not available :confused:
Do you see any alternative?

6

Hello @cesartozzi,

Can you try this one? Below is the DAX provided for the reference -

Color via DAX = 
VAR _Max_Date = 
CALCULATE( MAX( Table1[Date] ) ,
    ALLEXCEPT( Table1 , 
        Table1[product] ) )


VAR _Results = 
CALCULATE(
    CALCULATE( MAX( Table1[color] ) ,
        FILTER( Table1 ,
            Table1[Date] = _Max_Date ) ,
    FILTER( Table2 , 
        Table2[product] IN VALUES( Table1[product] ) ) ) )

RETURN
_Results

Thanks and Warm Regards,
Harsh

1 Like
7

Love how you provided solution on this thread @Harsh. Appreciate your help on it.

Hi @cesartozzi, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi @cesartozzi, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

9

That worked, thank you @Harsh .