topic how to select distinct values from a table in DAX Commands and Tips

how to select distinct values from a table

venkatt — Sun, 02 Aug 2020 20:59:56 GMT

Dear Community,

I have following scenario and appreciate your support.

Table : XXXXX

Sales order description LastChangeDate

0001 sales order1 01.08.2020

0002 sales order2 02.08.2020

0001 sales order1 12.07.2020

0002 sales order2 05.06.2020

I would like to select discting Sales orders ,descriptions and the latest "LastChangedDate".

Sales order description LastChangeDate

example expected results:

Sales order description LastChangeDate

0001 sales order1 01.08.2020

0002 sales order2 02.08.2020

Appriciate your solution .

Best Regards.

Ramana

Re: how to select distinct values from a table

az38 — Sun, 02 Aug 2020 21:26:46 GMT

venkatt

just put all your columns into visual table and set aggregation for date value as Last:

Re: how to select distinct values from a table

Anonymous — Sun, 02 Aug 2020 21:32:51 GMT

Might seem like a simple solution, however, are you able to use a slicer or are you looking for a Dax specific solution?
Try dragging the LastChangeDate into the slicer and visuals on your report will adjust to show the corresponding values.

Re: how to select distinct values from a table

amitchandak — Mon, 03 Aug 2020 03:22:12 GMT

venkatt , Try like

description = lastnonblankvalue(Table[LastChangeDate],max(Table[description]))

// Add this desc and rest two as same

//or this measure in place of date

Measure =
VAR __id = MAX ( 'Table'[ Sales order ] )
VAR __date = CALCULATE ( MAX( 'Table'[LastChangeDate] ), ALLSELECTED ( 'Table' ), 'Table'[ Sales order ] = __id )
RETURN CALCULATE ( Max ( 'Table'[LastChangeDate] ), VALUES ( 'Table'[ Sales order ), 'Table'[ Sales order] = __id, 'Table'[LastChangeDate] = __date )

Re: how to select distinct values from a table

venkatt — Mon, 03 Aug 2020 07:57:51 GMT

HI Amit ,

Thank you for the reply. Is it possible to create new table using dax and achive only the distinct values of sales orders along with only LastChangedDate.

Thanks in advance.

Re: how to select distinct values from a table

venkatt — Mon, 03 Aug 2020 07:59:38 GMT

Hi Carlos,

Thank you for the reply, i am looking for Dax solution , so that i can list the distint values into separate table and visualize only unique values.

Thanks,

Ramana