Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Tutorial Requests
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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 )

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

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.