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.

  • 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

      • MP_123's avatar
        MP_123
        Microsoft Employee
        9 years ago

        thanks v-haibl-msft!

        you're awsome!!!

         

        but one question - i want to calculate for each row in the table the count of users \ new measure

        and then to sum it all.

        the divide (sum(Count),new measure) isn't the correct result

        do you have an idea?

         

        thanks a lot!!