Forum Discussion

MP_123's avatar
MP_123
Microsoft Employee
9 years ago
Solved

count if

hi, please see this table. i want to add new measure that count for each row in the table if there are another row with the same : target date, market. etc.. (not all the columns) meaning, not...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    @MP_123

     

    Please try with following MEASURE formula. It should also work when you slice it.

     

    Same_Row_Count = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        ALLSELECTED ( Table1 ),
        SUMX (
            FILTER (
                Table1,
                EARLIER ( Table1[target date] ) = Table1[target date]
                    && EARLIER ( Table1[signal name] ) = Table1[signal name]
                    && EARLIER ( Table1[device] ) = Table1[device]
                    && EARLIER ( Table1[Count of Users] ) = Table1[Count of Users]
            ),
            Table1[Num]
        )
    )
)

     

    Best Regards,

    Herbert

MP_123's avatar
MP_123
Microsoft Employee
9 years ago

hi ankitpatira!

thanks for replying.

i don't want to keep or remove, i want to count rows, according to specific columns.

count if date=date, device= device, etc.

hope it's clear now

 

thanks!

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

MP_123 You can do something like below,

 

1. Create calculated column for each column you want to find duplicates for. This will give 1 if unique and 0 if duplicate.

 

duplicateColumn1 = IF(
CALCULATE(
COUNTA(TABLE[column]),
FILTER(TABLE, TALBE[column] = EARLIER(TABLE[column]))
)>1,
0,
1
)

 

2. Then use table visual and filter out 1's which will give you count of duplicate rows.