topic Re: New table with filtered distinct ID's in DAX Commands and Tips

New table with filtered distinct ID's

Anonymous — Wed, 12 May 2021 10:26:26 GMT

Hi Community,

I have a datatable with multiple months below each other. The datatable consists of the column 'period' and 'ContractID'.

The goal is to create a new table with only the distinct ContractID's from the last available month.

I tried multiple variations of lines of code, but none gave me the right answer.

Hopefully somebody here can help.

Thanks, Thom

Re: New table with filtered distinct ID's

Anonymous — Wed, 12 May 2021 11:53:30 GMT

1. Sample, representative data for the problem.

2. Transformation description.

3. Desired outcome.

Re: New table with filtered distinct ID's

Anonymous — Wed, 12 May 2021 12:10:03 GMT

Hi Daxer,

Sample datatable is:

Period ContractID
2021-01 1
2021-01 2
2021-01 3
2021-01 3
2021-02 2
2021-02 3
2021-02 3
2021-02 4

In this sample the last month is 2021-02, so I expect the new datatable outcome to be:

Period ContractID
2021-02 2
2021-02 3
2021-02 4

Hope this clarifies

Re: New table with filtered distinct ID's

Anonymous — Wed, 12 May 2021 12:43:30 GMT

// Data let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MFQwVIrVQfCMUHjGOHlGKCqN0ORQeSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Period ContractID" = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Period ContractID", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Period ContractID.1", "Period ContractID.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period ContractID.1", type date}, {"Period ContractID.2", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Period ContractID.1", "Period"}, {"Period ContractID.2", "ContractID"}}), #"Removed Duplicates" = Table.Distinct(#"Renamed Columns"), #"Calculate Max Date" = List.Max(#"Removed Duplicates"[Period], null), #"Should Keep" = Table.AddColumn( #"Removed Duplicates", "Should Keep?", each [Period] = #"Calculate Max Date" ), #"Filtered Rows" = Table.SelectRows(#"Should Keep", each ([#"Should Keep?"] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Should Keep?"}) in #"Removed Columns"

Re: New table with filtered distinct ID's

PaulOlding — Wed, 12 May 2021 12:44:21 GMT

Hi Thomvdw,

New Table =

VAR _MaxPeriod = MAX('Table'[Period])

RETURN

CALCULATETABLE(

SUMMARIZE('Table', 'Table'[Period], 'Table'[ContractID]),

'Table'[Period ] = _MaxPeriod

)