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

Top Name with count

Hi,

 

I'm trying to find the top partner name from the table below. 'Top' as in most leads, so I'm counting the leads per partner and wanting to return the name of the partner with the most leads.

I'm getting an error with the below code 'cannot convert value 'Tom' of type text to type true/false'.

 

I'd also like to be able to find the second best partner name as a separate query. Can you help please?

 

Top Partner = calculate(values('Leads+Exceptions'[PartnerName]),
filter(values('Leads+Exceptions'[PartnerName]),
if(
rankx(
all('Leads+Exceptions'[PartnerName]),count('Leads+Exceptions'[LeadKey]),,DESC)=1,
'Leads+Exceptions'[PartnerName],BLANK()
)

))

 

I also tried with this formula that I got from thread 'Dax top 1 name' in this forum:

 

Top ISV = calculate(max('Leads+Exceptions'[PartnerName]),
filter(values('Leads+Exceptions'[PartnerName]),
rankx(
all('Leads+Exceptions'[PartnerName]),count('Leads+Exceptions'[LeadKey]),,desc)<=2),USERELATIONSHIP('Date'[calendar_date],'Leads+Exceptions'[LeadMonth])

)

 

It gave no errors, but it's not giving the correct result. Perhaps it's not 'grouping' the names together before doing a count, so it's counting each row as 1? My table is akin to the below but obv much bigger!

 

sampledata.JPG

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

@Anonymous

 

how do you want to return it? in a measure? as a table?

 

here is a calculated table that will return the top 1

 

 

Table =
TOPN (
    1,
    SUMMARIZECOLUMNS (
        'Leads+Exceptions'[PartnerName],
        "Count"COUNTROWS ( 'Leads+Exceptions' )
    ),
    [Count], DESC
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

As a measure.

 

I'd want it to be adaptable so I can change it to find the 2nd as well (in a separate measure).

 

Thanks

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.