Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

calculated column with VALUES function on another table being filtered by slicer

I have 2 tables. first is "registered_users" which is all of my currently registered users name user_id alex 1 sarah 2 joe 3 bill 4 second is "1year_user_activity" which is a...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    hi  Anonymous 

    Just adjust the formula as below:

    registered_user_activity_rate = 
DIVIDE (
    CALCULATE(DISTINCTCOUNT ( '1year_user_activity'[user_id] ),FILTER('1year_user_activity','1year_user_activity'[user_id] in VALUES(registered_users[user_id]))),
    COUNT ( 'registered_users'[user_id] )
)

    Result:

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
5 years ago

thank you v-lili6-msft. The main problem is that the numerator should be only the users in the 1year_activity_table who are also in the registered_users table. I have attached the sample pbix here, and you can see that, if we select buy, the result of the measure is 33%, but it should only be 16.66% because, in the filtered 1year_activity table, there is only 1 user who is also in the registered users table (6 registered users), therefore 1/6.

 

pbix_sample 

v-lili6-msft's avatar
v-lili6-msft
Community Support
5 years ago

hi  Anonymous 

Just adjust the formula as below:

registered_user_activity_rate = 
DIVIDE (
    CALCULATE(DISTINCTCOUNT ( '1year_user_activity'[user_id] ),FILTER('1year_user_activity','1year_user_activity'[user_id] in VALUES(registered_users[user_id]))),
    COUNT ( 'registered_users'[user_id] )
)

Result:

 

Regards,

Lin