Automatic dax for duplicates certain value to new table

[Delion]

Hi 

I have table A and I want to create table B , which is only keeping latest value from max=index using dax

Is it possible ?

many thanks

[v-yiruan-msft]

Hi  @Delion ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editior by the following codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pcmxDQAgCATAXagt4BFlF+L+a6iN9NhdchGkLNQIDLCJHaoarVaLnjE/Y2b4ZyBjXCqXw19A6rE2", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, DATE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"DATE", Int64.Type}, {"VALUE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"VALUE"}, {{"MaxIndex", each List.Max([Index]), type nullable number}, {"Details", each _, type table [Index=nullable number, DATE=nullable number, VALUE=nullable number]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Index", "DATE"}, {"Index", "DATE"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Details", "Display", each if [MaxIndex]=[Index] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Display] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxIndex", "Display"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Samarth_18]

Hi @Delion ,

You could create a measure as below and use it as visual level filter:-

Measure = 
if(MAX('Table'[Index]) = CALCULATE(MAX('Table'[Index]),ALL('Table')),1,0)

Thanks,

Samarth

[Delion]

Thank @Samarth_18 for replied 

I tried your measure, but new column showing as 1 for all rows 

[Samarth_18]

@Delion , Please create it as measure instead of column.

[Delion]

Still same, it's only have one value  = 1, no 0 in measure

[Samarth_18]

@Delion ,Since all the details of three records are same thats why it will display only 1 record on visual. If you have any other column which can act as distinct then you have to add that column as well on visual.