Count the no. of occurrences of id's in a table using calculated column

[Anonymous]

Hi All,

I Have a table with:  id, user_id , created_date

I tried to find the no. of times user appers in the table using this:

CountUsers = CALCULATE(
    COUNTA('public accounts_usersession'[user_id]);
    FILTER('public accounts_usersession';
        'public accounts_usersession'[user_id] = EARLIER('public accounts_usersession'[user_id])))

 But when I am filtering the data 'yearly' it still shows me the whole value and not the changed value. 

When I selected the year in the filter, it's giving me the same count for the users (in the example user_id = 601).

I desired output here should be 5 for user_id = 601 in the year 2018.

[Veles]

I'm guessing you've made this as a calculated column?

You should be able to do this without making a DAX expression at all.

Just put your user_id into the values field of a visual and click on the little arrow next to it and select Count (Distinct).

Give that a go and if it's not what you need then there'll be a DAX expression that will work.

EDIT: Looking at your data you might just need Count rather than Count (Distinct)

[Anonymous]

@Veles Thank you for your reply. 

Yes I made this as a calculated column because I calculated the user_distribution based on this column. That you can see in the image. 

User distribution:

User_Distribution = IF('public accounts_usersession'[CountUsers] = 1 ; "1x";
                    IF('public accounts_usersession'[CountUsers] = 2 ; "2 -3x";
                    IF('public accounts_usersession'[CountUsers] = 3 ; "2 -3x";
                    IF('public accounts_usersession'[CountUsers] = 4 ; "4 -5x";
                    IF('public accounts_usersession'[CountUsers] = 5 ; "4 -5x";
                    IF('public accounts_usersession'[CountUsers] = 6 ; "6 -10x";
                    IF('public accounts_usersession'[CountUsers] = 7 ; "6 -10x";
                    IF('public accounts_usersession'[CountUsers] = 8 ; "6 -10x";
                    IF('public accounts_usersession'[CountUsers] = 9 ; "6 -10x";
                    IF('public accounts_usersession'[CountUsers] = 10 ; "6 -10x"; "> 10"))))))))))

Final destination is to play around with the user_distribution which depends on the Countusers.

[Veles · Accepted solution]

Ah you'll need to make this a measure rather than a column. Columns are always static info.

Wouldn't be very different from what you already have:

CountUsers = COUNTA('public accounts_usersession'[user_id])

No need to have the filter expression.

Then you would have something along the lines of:

User_Distributuion = 
           IF([CountUsers] = 1, "1x",
           IF([CountUsers] = 2 || [CountUsers] = 2, "2 -3x",
.....

Which returns something like this:

Your bucket measure can only be used in the "Values" section of a visual. Gets a bit more complicated if you want to use these buckets as an axis on a graph.