March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
Each employee has a PerformanceID where 3 = 'Outstanding'
If I put this into a table I get:
If I then add a 'Recruitment campaign' column I get:
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.
Solved! Go to Solution.
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] )
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 @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] )
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |