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 )

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
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 )

 

MylèneB's avatar
MylèneB
Helper II
5 years ago

Hello!

How would do it so the number adds up automatically instead of counting 1 each time? So to have kind of an index without the duplicates Id in it?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Share some data and show the expected result clearly.

    • MylèneB's avatar
      MylèneB
      Helper II
      5 years ago

      Hi, sorry for my unclear question yesterday.

      I have a table with a few names and ids. I want to add a number to similar names if their id are different, so we can see quickly that it's 2 different persons. 

       

      ID      NAME          RESULT

      654 John Smith  John Smith

      123 John Smith  John Smith2

      123 John Smith  John Smith2

      789 Jane Doe     Jane Doe

       

       

      I thought of using this solution to count distinct value and then concatene with the names but it's not working since only the first name have a different value. Is it clearer? Thanks a lot for your help!

      • 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"),
    #"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.