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

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