Forum Discussion

mmurph16's avatar
mmurph16
New Member
3 years ago

Distinct Values

Hello!

 

we had a tricky issue with counting number of duplicate rows in our BI table, we are having a really tough time formulating a DAX code to generate differentiating repeating values. So basically what we want to do is for unique values to display "0" then for multiple repeating numbers, we want to integrate a count value. So if there are repeating values, we want the new row to give a them seperate value. Shown below in our "test" table in excel, the "count" column gives us a count of repeating number. Anyway anyone can help with implementing a formula that would work for us Power Bi?

Thanks

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I don't know how you'd do it in DAX, but in Power Query, you could first Group By Employee Number, use a Count aggregation (let's name it "RowCount" and an All Rows aggregation (let's name it "Details", and then add an Index (beginning at 1) to each of the nested tables, and subtracting 1 from each Index value where the count of rows is 1. So after the Group By step:


    Table.TransformColumns(PriorStepOrTableName, "Details", each Table.AddIndexColumn(_, "Count", 1, 1), Int64.Type)

     

    Now you can expand the column, but your single row value will be 1 instead of zero. So now you can go


    Table.AddColumn(PriorStepOrTableName, "CorrectCount", each if [RowCount] = 1 then 0 else [Count])

     

    --Nate

    Good to go!