topic Re: DAX to summarize a table bringing only the last date of duplicate records. in DAX Commands and Tips

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

Oscar_Mtz_V — Sun, 27 Oct 2019 09:01:11 GMT

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]
)

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

Zubair_Muhammad — Mon, 28 Oct 2019 04:20:20 GMT

Hi Oscar_Mtz_V

Try following

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

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

Oscar_Mtz_V — Tue, 29 Oct 2019 14:07:45 GMT

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.

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

Anonymous — Thu, 30 Jan 2020 09:51:36 GMT

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?

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

mapr — Tue, 17 Oct 2023 15:58:09 GMT

This is just I need, thanks.