DAX to summarize a table bringing only the last date of duplicate records.

[Oscar_Mtz_V]

Hi, I have "TABLE 1" where I have duplicate records, the issue is that while summarizing for getting unique records, as someone missepelled the name, I am getting two records for a single ID, as in case of ID 8 on my "Current Result" Table, this is incorrect.

The ideal would be to have something like the "DESIRED RESULT" table.

I need to used DAX as I do not have access to the data source for doing this from the Query.

DAX I am currently using, which is not working is:

RESULT = 
SUMMARIZE(
    'TABLE1',
    'TABLE1'[ID],
    'TABLE1'[NAME]
)

[Zubair_Muhammad]

Hi @Oscar_Mtz_V 

Try following

RESULT =
FILTER (
    'TABLE1',
    'TABLE1'[DATE]
        = CALCULATE (
            MAX ( 'TABLE1'[DATE] ),
            ALLEXCEPT ( TABLE1, 'TABLE1'[NAME], 'TABLE1'[ID] )
        )
)

[Oscar_Mtz_V · Accepted solution]

Thanks @Zubair_Muhammad , although your DAX proposal was not 100% of what I needed it guide me through to get the right result, DAX can be found below.

RESULT =
FILTER (
    'TABLE1',
    'TABLE1'[DATE]
        = CALCULATE (
            MAX ( 'TABLE1'[DATE] ),
            ALLEXCEPT ( TABLE1, 'TABLE1'[ID] )
        )
)

This was because the "ID" is the column that contained all the right ID's, no typos.

[mapr]

This is just I need, thanks.

[Anonymous]

Hey guys,

I believe i have the same issue, but i got this message...

My measure is:

Result =

FILTER (

'Main Calendar',

'Main Calendar'[Date]

= CALCULATE (

MAX ('Main Calendar'[Date]),

ALLEXCEPT ( 'Main Costs', 'Main Costs'[Department], 'Main Costs'[Item], 'Main Costs'[Item Name], 'Main Costs'[Family], 'Main Costs'[Sub-Family], 'Main Costs'[Qty], 'Main Costs'[Un Price],'Main Costs'[NetValue],'Main Costs'[VatRt], 'Main Costs'[GrossTotal] )

)

)

Any ideas?