Forum Discussion

Noexicaise's avatar
Noexicaise
Frequent Visitor
6 years ago
Solved

Count Text Occurence

Hello,

 

I'm trying to get the top 10 occurrences of a column (see below)

 

Table 


The format is text.


Ideally, I would like to get the following result:

 

Result 

 

In this example, Laptop = "5242"  and Windows User Profile = "5003"

 

Thx in advance

 

Regards

 

Julien

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Noexicaise ,

     

    For your issue, maybe you can do like this:

    //create a column in the original table; the Sales[Brand]field is the counted column
Number = 
CALCULATE(
    COUNT(Sales[Brand]),
    ALLEXCEPT(
        Sales, Sales[Brand]
    )
) 
// return the count table
Count_occurence = 
TOPN(
    10,
    DISTINCT(Sales),
    Sales[Number],
    ASC
)

     

    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.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Noexicaise ,

     

    For your issue, maybe you can do like this:

    //create a column in the original table; the Sales[Brand]field is the counted column
Number = 
CALCULATE(
    COUNT(Sales[Brand]),
    ALLEXCEPT(
        Sales, Sales[Brand]
    )
) 
// return the count table
Count_occurence = 
TOPN(
    10,
    DISTINCT(Sales),
    Sales[Number],
    ASC
)

     

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

    Do you have a table with columns showing you which number goes with what name? Like:

     

    NameNumber
    Laptop5242

     

    If so, you can use a table visual adding 2 columns, one column shows you the "Name" and the other column shows you "Count of Name". You might have to add some relationships if you have more than 1 table.