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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Noexicaise
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-lionel-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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