Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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":
Solved! Go to 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
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
@Anonymous , in case you are using slicer to filter values and see % of that
divide(count(Table[user]), calculate(count(Table[user]), all(Table)))
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.