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

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

MP_123 If you go to power bi desktop query editor -> you get option to keep or remove duplicates and you can highlight columns for which you want to keep / remove duplicates.

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.

    • MP_123's avatar
      MP_123
      Microsoft Employee
      9 years ago

      ankitpatira thank you

       

      i tried this calculated column:

      Column =
      CALCULATE(COUNTROWS(' Test_Table2'),
      FILTER(' Test_Table2',
      ' Test_Table2'[target date]=EARLIER(' Test_Table2'[target date]) &&
      ' Test_Table2'[signal name]=EARLIER(' Test_Table2'[signal name]) &&
      ' Test_Table2'[device]=EARLIER(' Test_Table2'[device]) &&
      ' Test_Table2'[Count of Users]=EARLIER(' Test_Table2'[Count of Users])
      )
      )

       

      but i see that the column isn't changing dynamically. is it make sense? the values are constant no matter how i slice it.

       

      regarding your solution: i think it won't work since i want to compare whole combination, and not only one at a time

       

      thanks again

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        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