count if

[MP_123]

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!

[mtgm]

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 🙂

[ankitpatira]

@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]

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]

@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]

@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 · Accepted solution]

@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]

hi @v-haibl-msft

can you please help me understand the formula you suggest? i don't understand the use of all these functions.

thanks a lot!

[v-haibl-msft]

@MP_123

Since you want to the result can change dynamically, we need to use measure here. To count the almost same rows, we need to use EARLIER function. I’d like to recommend this article to you for the principle of EARLIER function. However EARILER is usually used in column but not measure. So we need to some changes according to this article. I think it will be enlightened after you read through these two articles.

Best Regards,

Herbert

[MP_123]

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!!