Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional Row count

Hello guys, I'm newbie to PowerBI and I need some logical help.   I want to count each unique row value as '1' or if duplicate '0". Would appreciate your help.    Thanks, James
Need Help
  • PurpleGate's avatar
    PurpleGate
    4 years ago

    Hi, hope this helps

     

    In Transform Data:

     

    Add index starting from 0

     

    Group rows by ID, or whatever column you want to be the unique identifier

     

     

    Expand the table, put the index back in order of ascending then you can delete that column

     

     

    I then added a conditional column called "duplicated" which I would use in a measure

     

     

    Then I can see how many times each of rows [Product with the same ID] appear in my table.

    The measure was

    Measure = calculate(COUNTROWS('Table'),FILTER('Table','Table'[Number_of_Items]>1))
     
     

    This was my original table:

        

     

     

PurpleGate's avatar
PurpleGate
Icon for Resolver III rankResolver III
4 years ago

Hi, hope this helps

 

In Transform Data:

 

Add index starting from 0

 

Group rows by ID, or whatever column you want to be the unique identifier

 

 

Expand the table, put the index back in order of ascending then you can delete that column

 

 

I then added a conditional column called "duplicated" which I would use in a measure

 

 

Then I can see how many times each of rows [Product with the same ID] appear in my table.

The measure was

Measure = calculate(COUNTROWS('Table'),FILTER('Table','Table'[Number_of_Items]>1))
 
 

This was my original table: