DISTINCT count for first value DAX

[Anonymous]

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
69260	0
69260	0
68440	1
54098	1
54098	0
54098	0
54098	0
37581	1
85021	1
72878	1
62765	1
62765	0
62765	0
62765	0
62765	0
62765	0
62765	0
59170	1
50076	1
50076	0
75400	1
37986	1
37986	0
37986	0
37986	0
37986	0
56591	1
42499	1
52460	1

[v-eachen-msft · Accepted solution]

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]

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]

Hi,

Share some data and show the expected result clearly.

[MylèneB]

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]

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.

[MylèneB]

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.

[Ashish_Mathur]

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.

[MylèneB]

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 

[amitchandak]

Distinct count function is there for this

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

M1= distinctcount(ID)

M1= Calculate (distinctcount(ID))

Have I missed something?

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

it's giving the sum total ID or 1 value. or it's not ignoring the second value

[amitchandak]

If you need exact same display, what you have give. Then first need a unique ID . Add index column .

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

In new office ribbon, edit queries is transform data

Then create a column like

Column = if(ISBLANK( COUNTX(FILTER(countDistinct,countDistinct[ID] = EARLIER(countDistinct[ID]) && countDistinct[Index]<EARLIER(countDistinct[Index])),countDistinct[ID])),1,0)

refer

https://www.dropbox.com/s/m5s2lg2jpe59nap/DistinctData.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

.

[Anonymous]

@amitchandak @az38 @TomMartens  can you check this issue

[Anonymous]

@mwegener