Forum Discussion
calculated column with VALUES function on another table being filtered by slicer
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
hi Anonymous
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, for your requriement, you could use this formula to create a meausre
Result =
DIVIDE (
DISTINCTCOUNT ( '1year_user_activity'[user_id] ),
COUNT ( 'registered_users'[user_id] )
)
iF you still have problem, please share your sample pbix file and your expected output.
Regards,
Lin
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.
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
Thank you v-lili6-msft , this solved the problem perfectly. I didn't understand how to use the CALCULATE function correctly