Get the distinct count of "each" user in the column

[Anonymous]

I am trying to get the distinct count of "each" user in the column "Ticket opened by", any help on this please...

I am trying to find the numbers for only few users from the list.

There are more than 1000 users in that list, i just want 50 hand picked users (not top 10) but Unique individuals.

[amitchandak]

Not very clear.

you can use distinctcount(Table[Usename]) in measure and can get

[Anonymous]

@amitchandak I tried that already, that does not serve the purpose.

My problem is: I have a column with 1000+ names in it which also have unique TKT number for each row in another column.

I am trying to find out the count of TKT numbers for few of them from the list (Not Top 10), but get the count of TKT numbers for few of them in the list of users from the 1000+ list.

[amitchandak]

@Anonymous , can you explain with an example.

[Anonymous]

@amitchandak 

Check the above attached image.

Column A: Ticket Numbers

Column B: Names

I am trying to get Count of Ticket numbers for few of them from Column B.

Ex: Number of ticket for Zhou, Lin

[amitchandak]

@Anonymous ,

If you put Ticket opened by and Disctinctcount(table[ticketnumber]) in a matrix or table, is it not giving you the information you wanted.

[Anonymous]

@amitchandak I have tried that, but it gives me the count of everyone in the list. But as I told that does not serve the purpose, I am trying to get Count of Ticket numbers for few of them from Column B, not all and not Top N, I am looking for few hand picked from that list of users.

[v-lionel-msft]

Hi @Anonymous ,

I made a data model like this:

First, I used "Remove Duplicates rows" in Edit Queries and got this:

Second, I created a table visual and a slicer visual:

"I am looking for few hand picked from that list of users."

I don't understand the rules you would like to use to select users in the list. Maybe you can use the slicer and enable the multi-select function.

Best regards,
Lionel Chen

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

[Anonymous]

@v-lionel-msft This will also not work for me as the name field is fixed for me on my Dashboard, which is flowing in from a different excel sheet..

[v-lionel-msft]

Hi @Anonymous ,

I took a closer look, and my understanding is that you want to find out who has the unique [Ticket Number] in the list, maybe you can create a measure:

Measure = 
VAR x = 
CALCULATE(
    COUNT(Sheet1[Names]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[Names]
    )
)
RETURN
IF(
    x = 1,
    MAX(Sheet1[Names]),
    BLANK()
)

Best regards,
Lionel Chen

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

[Anonymous]

@v-lionel-msft I am sorry, but no.

I am looking to Get the distinct count of "each" user in the column and i have a "Fixed" user which i have in a different sheet... I want to get the distinct count of each user (Handpicked few(say 30) from the list which has 1000+ users)... I want the numbers to show only for those 30 users not all of them....

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

"I want to get the distinct count of each user (Handpicked few(say 30) from the list which has 1000+ users)"

Sorry, I still don't quite understand. I'm not sure what rules you want to use to filter out these 30 people.

Maybe what you want is this?

Or this?

Best regards,
Lionel Chen

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