topic Re: Extracting top 1 information to a card in DAX Commands and Tips

Extracting top 1 information to a card

Anonymous — Fri, 12 Aug 2022 06:19:37 GMT

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:

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.

Re: Extracting top 1 information to a card

tamerj1 — Fri, 12 Aug 2022 06:28:45 GMT

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] )

Re: Extracting top 1 information to a card

Anonymous — Fri, 12 Aug 2022 06:40:44 GMT

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.

Re: Extracting top 1 information to a card

Anonymous — Wed, 17 Aug 2022 07:07:03 GMT

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

Re: Extracting top 1 information to a card

tamerj1 — Wed, 17 Aug 2022 07:24:01 GMT

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

Re: Extracting top 1 information to a card

Anonymous — Wed, 17 Aug 2022 08:09:57 GMT

Hi tamerj1 ,

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

Is it something like this?

Thanks for your help.

Re: Extracting top 1 information to a card

tamerj1 — Wed, 17 Aug 2022 08:35:07 GMT

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

Re: Extracting top 1 information to a card

Anonymous — Wed, 17 Aug 2022 08:57:29 GMT

Hi tamerj1 ,

I solved it!
Thanks for your help.