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

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

  • 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