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

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

 

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

    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