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

nameuser_id
alex1
sarah2
joe3
bill4

second is "1year_user_activity" which is all of the activity by users (currently registered or not)

user_idactivity
1sell
3buy
1buy
2browse
5browse

 

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

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

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