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 if there are exact the same row, but almost.

in SQL i would join twice the same table and compare a.target date = b. target date

i can't write in in the query since i want it to calculate dynamically across slicers.

 

is someone please have an idea??

 thanks a lot!

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

9 Replies

  • 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.

  • mtgm's avatar
    mtgm
    New Member
    5 years ago

    Hello 🙂

     

    I did an online questionnaire and I have a few questions with more than one answer.
    For example:
    Drink; Eat; Sleep
    Eat
    Drink; Sleep, Dress

    So, I want to count how many times each word appears.
    In excel I use countif. In case you want to know how many times the word "Drink" appears: COUNTIF (TABLE; "DRINK")

    How can I do this in PowerBI?
    Thanks 🙂