Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 

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:

1.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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
Not applicable

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors