CODEWITHSUNDEEP
powerbidaxcalculated-columns
Ryan
Ryan

Reputation: 129

Power bi - Getting the most recent value from another table

Hi I'm a beginner in Power bi

enter image description here

I have two tables Table 1 has Dates, Unique IDs and Emails

Table 2 has list of Unique IDs same as the ones in table 1

Using DAX, I want to add a calculated column to the 2nd table to get the most recent email from table 1 based on the unique id and also avoid Email Values

Thank you.

Upvotes: 0

Views: 8050

Answers (2)

smpa01
smpa01

Reputation: 4346

with DAX measure - if both tables have a relationship

Measure1 = 
CALCULATE (
    MAX ( 'Table 1'[Email] ),
    FILTER (
        'Table 1',
        'Table 1'[Date]
            = CALCULATE (
                MAX ( 'Table 1'[Date] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Unique_ID] )
            )
    )
)

with DAX calculated column - if both tables have a relationship

Column1 =
CALCULATE (
    CALCULATE (
        MAX ( 'Table 1'[Email] ),
        FILTER (
            'Table 1',
            'Table 1'[Date]
                = CALCULATE (
                    MAX ( 'Table 1'[Date] ),
                    ALLEXCEPT ( 'Table 1', 'Table 1'[Unique_ID] )
                )
        )
    )
)

s1

s2

with DAX measure - if both tables have no relationship

Measure2 =
CALCULATE (
    MAX ( 'Table 1'[Email] ),
    FILTER (
        'Table 1',
        'Table 1'[Date]
            = CALCULATE (
                MAX ( 'Table 1'[Date] ),
                ALLEXCEPT ( 'Table 1', 'Table 1'[Unique_ID] )
            )
    ),
    TREATAS ( VALUES ( 'Table 2'[Unique_ID] ), 'Table 1'[Unique_ID] )
)

with DAX calculated column - if both tables have no relationship

Column2 = 
CALCULATE (
    CALCULATE (
        MAX ( 'Table 1'[Email] ),
        FILTER (
            'Table 1',
            'Table 1'[Date]
                = CALCULATE (
                    MAX ( 'Table 1'[Date] ),
                    ALLEXCEPT ( 'Table 1', 'Table 1'[Unique_ID] )
                )
        ),
        TREATAS ( VALUES ( 'Table 2'[Unique_ID] ), 'Table 1'[Unique_ID] )
    )
)

s3

s4

Upvotes: 4

Marco_CH
Marco_CH

Reputation: 3294

Oh sorry I just realized you're already having a table2. Now I've split the answer in two party. New Column and New Table.

Creating NEW COLUMN

In this case you could try:

Most_Recent_Mail = SELECTCOLUMNS(TOPN(1,FILTER(table1, table1[Unique_ID] = table1[Unique_ID]), table1[Date]), "mail", Tabelle1[mail])

Creating NEW TABLE

I copied your table:

enter image description here

Now we can create a new table:

enter image description here

And use the following DAX code to fill this table:

New_Table = SUMMARIZE(table1,table1[Unique_ID], "Most_Recent_Mail", 
            CALCULATE(VALUES(table1[mail]), FILTER(all(table1), table1[Date] = max(table1[Date]))))
  • SUMMARIZE is like a group function.
  • With CALCULCATE you can create expressions in a filtered context.
  • With FILTER you can create a new context/subset.
  • ALL is ignoring all existing filters an returns the full dataset.

Result:

enter image description here

PS: Just to understand. If you only wanted the date, the formula would be much shorter

table2 = SUMMARIZE(table1,table1[Unique_ID], "Max_Date", 
            max(table1[Date]))

And if you would like to have both, you can add as much columns you want:

New_Table = SUMMARIZE(table1,table1[Unique_ID], 
            "Max_Date", max(table1[Date]), 
            "Most_Recent_Mail", 
            CALCULATE(VALUES(table1[mail]), FILTER(all(table1), table1[Date] = max(table1[Date]))))

enter image description here

Upvotes: 0

Related Questions