Forum Discussion
DISTINCT count for first value DAX
Help me to create the calculated column for the DISTINCT count for first value DAX if id repeats multiple time we have to consider as 1 ID Result 69260 1 69260 0 69260 0 69...
Hi Anonymous ,
At first, you need to add an index column in the query editor.
Then you could create a new column to get the result.
Column = VAR a = CALCULATE ( FIRSTNONBLANK ( 'Table'[ID], 1 ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) RETURN IF ( a = 'Table'[ID], 0, 1 )
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"ID", type text}}, "en-IN"),{"ID", "Name"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"GroupTables", each _, type table [Merged=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables])),
#"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"Merged"}, {"Merged.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GroupTables",{"Merged"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged.1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Merged.1.1", "ID"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Merged.1.2", "Merged.1.2 - Copy"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"CountRows", type text}}, "en-IN"),{"Merged.1.2", "CountRows"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Renamed Columns1" = Table.RenameColumns(#"Merged Columns1",{{"Merged", "Result"}, {"Merged.1.2 - Copy", "Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"ID", "Name", "Result"})
in
#"Reordered Columns"Hope this helps.
Hi,
I think we are getting close, but in this way if I have 3 times John Smith, it will put John Smith3 since we are counting rows. It's not yet what I'm looking for. I thought thera was a way to use the solution you showed on this page to solve my problem, but maybe I should do an distinct question to help.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"ID", type text}}, "en-IN"),{"ID", "Name"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"GroupTables", each _, type table [Merged=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables])), #"Expanded GroupTables" = Table.ExpandTableColumn(#"Added Custom", "GroupTables", {"Merged"}, {"Merged.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded GroupTables",{"Merged"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged.1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Merged.1.1", "ID"}}), #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Merged.1.2", "Merged.1.2 - Copy"), #"Added Custom1" = Table.AddColumn(#"Duplicated Column", "Custom", each if [CountRows]=1 then null else [CountRows]), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom1", {{"Custom", type text}}, "en-IN"),{"Merged.1.2", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns1",{{"Merged", "Result"}, {"Merged.1.2 - Copy", "Name"}}), #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"CountRows"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"ID", "Name", "Result"}) in #"Reordered Columns"Hope this helps.
Hi thank you for your help! It doesn't seem to work when I do it, it counts the number of rows the name appear in so I have a few John Smith3 or Jane Doe2
I did a different post since it wasn't related to this one ultimately and there is a calculated column that is working: https://community.powerbi.com/t5/Desktop/Rename-people-based-on-unique-ID/m-p/1886078#M724424