Handling duplicates

[UsePowerBI]

Hello

I have this Table1:

ID	Type	Status	Date
123	A	Ongoing	01/04/2020
123	B	Cancelled	03/04/2020

And I want to calculate the 2,3,4th column of the Table2 below:

ID	Type	Status	Date
123	B	Cancelled	03/04/2020
123	B	Cancelled	03/04/2020

So basically,  I want for every ID in Table2[Col1], I want to search in Table2 and return the latest Type, the latest Status and the latest Date.

Please note that in every field of each of the tables above, there may be duplicates. I tried some formulas but I get a 'table returned instead of single value' error.

Any idea?

Thanks

[v-xuding-msft · Accepted solution]

Hi @UsePowerBI ,

I think the function that you used returns a table. You need to create a calculated table to return them all together.  Or create formulas get the results one by one.

I create the measures. Please check if it is what you want.

Latest Date = 
CALCULATE(
    MAX('Table 1'[Date]),
    FILTER(
        'Table 1',
    'Table 1'[ID] = MAX('Table 2'[ID]) 
    && 'Table 1'[Date] = MAX('Table 1'[Date])))

Latest Status = 
CALCULATE(
    MAX('Table 1'[Status]), 
    FILTER(
        'Table 1',
    'Table 1'[ID] = MAX('Table 2'[ID]) 
    && 'Table 1'[Date] = MAX('Table 1'[Date])))

Latest Type = 
CALCULATE(
    MAX('Table 1'[Type]),
    FILTER(
        'Table 1',
    'Table 1'[ID] = MAX('Table 2'[ID]) 
    && 'Table 1'[Date] = MAX('Table 1'[Date])))