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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Extracting top 1 information to a card

Hello All,

 

I have 4 recruitment campaigns and want to display on a card the campaign that is producing the most outstanding employees in the last 3 years.

So far I have a measure that returns the total number of outstanding employees across all campaigns:

 

Outstanding employees =
CALCULATE(COUNTROWS('Recruitment Data'),'DIM Performance'[PerformanceID] = 3,'Date Table'[Date] > (TODAY() - 1095))
 

Each employee has a PerformanceID where 3 = 'Outstanding'

 

If I put this into a table I get: 

 

brownrice_0-1660284883390.png

If I then add a 'Recruitment campaign' column I get:

 

brownrice_1-1660284941419.png

Ultimately, I want my card to display "Campaign 1, 281" however I can't get my head the way to do this. I assume it may be using the TOPN function and but can't get my head around the way to approach this.

 

Help would be greatly appreciated.
Thanks.

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 
Please try

Best Campaign =
VAR SelectedCampaigns =
    ALLSELECTED ( 'Recruitment Data'[RecruitmentScheme] )
VAR T1 =
    ADDCOLUMNS ( SelectedCampaigns, "@Outstanding", [Outstanding employees] )
VAR T2 =
    TOPN ( 1, T1, [@Outstanding] )
RETURN
    CONCATENATEX ( T2, [RecruitmentScheme] & ", " & [@Outstanding] )

View solution in original post

7 REPLIES 7
Kim_Sky
Helper II
Helper II

Hi @tamerj1 , 

I have the same problem and the code is really usefeul, but my value will be contains decimal, after applying the code, it shown text, how should I change it to whole number.

 

Thanks so much

Hi @Kim_Sky 
You can replace CONCATENATEX with MAXX and delete the delimiter part

Hi @tamerj1 ,

RETURN
    MAXX( T2, 'Category list'[Category] & ", " & [@TotalGas] & " kWh")

Is it something like this?

 

Thanks for your help.

 

Hi @Kim_Sky 
You are still concatenating using & therefore the results will be a text. Please give me an example of what are trying to achieve.

Hi @tamerj1 ,

 

I solved it! 
Thanks for your help. 

tamerj1
Super User
Super User

Hi @Anonymous 
Please try

Best Campaign =
VAR SelectedCampaigns =
    ALLSELECTED ( 'Recruitment Data'[RecruitmentScheme] )
VAR T1 =
    ADDCOLUMNS ( SelectedCampaigns, "@Outstanding", [Outstanding employees] )
VAR T2 =
    TOPN ( 1, T1, [@Outstanding] )
RETURN
    CONCATENATEX ( T2, [RecruitmentScheme] & ", " & [@Outstanding] )
Anonymous
Not applicable

Perfect! I can't say I understand the code yet (although I will put in the work to do so) but worked exactly as I'd hoped.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.