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

[Anonymous]

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 all of the activity by users (currently registered or not)

user_id	activity
1	sell
3	buy
1	buy
2	browse
5	browse

My goal is to have a "active user" percentage which can be sliced by the type of activity. I.e. what percentage of my currently registered users appear in the "user activity" table, and then change that percentage depending on which activity(s) are selected on the slicer.

If I select browse and buy, then i should see 50% because currently 2/4 registered users have used browse or buy.

The way I attempted to do this was with a calculated column and measure, both on the "registered_users" table.

calculated column "has_used_activity":

IF('registered_users'[user_id] in VALUES('1year_user_activity'[user_id]),1,0)

mesaure "activity_rate":

SUM('registered_users'[has_used_activity])/COUNT('registered_users'[user_id])

I then put a slicer on the activity, but the slicer doesn't affect the calculated column.

I have now read in other places that this is expected behavior, but I am wondering how to get the behavior "I" expect.

[v-lili6-msft]

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

[Anonymous]

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 · Accepted solution]

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]

Thank you @v-lili6-msft , this solved the problem perfectly. I didn't understand how to use the CALCULATE function correctly

[amitchandak]

@Anonymous , in case you are using slicer to filter values and see % of that

divide(count(Table[user]), calculate(count(Table[user]), all(Table)))

[Anonymous]

I don't think i understand the suggestion? My denomenator should be the count of all users in the "registered_user" in the table and my numerator should be distinct count of users in the "1year_user_activity" table that are also in the "registered_user" table. This numerator should be changing based on the slicer on activity type.