topic Top 10 with Multiple Columns in DAX Commands and Tips

Top 10 with Multiple Columns

saud968 — Thu, 27 Jul 2023 14:59:42 GMT

I have multiple columns and I am trying to get the dynamic top 10 with NOC Completed Rate and FTR Percentage, however, while creating RANKX Dax is getting an error "special flag is not allowed as an argument".

How do I get the TOP 10 with the columns mentioned above?

@v-yangliu-msft, amitchandak, Ahmedx , Ashish_Mathur

Re: Top 10 with Multiple Columns

saud968 — Thu, 27 Jul 2023 14:12:23 GMT

I was able to get Rank with

Rank NOC Completed = RANKX(ALL(TICKET_LIFECYCLE_FACT[FCD_FAMILYNAME]), [FTR PERCENTAGE],,DESC)
Struggling on Top 10 DAX, please help

tamerj1 amitchandak eliasayyy johnt75 Ashish_Mathur

Re: Top 10 with Multiple Columns

saud968 — Thu, 27 Jul 2023 16:24:16 GMT

I tried this but getting a blank value

Top N by Family Name =

var selected_top = SELECTEDVALUE(TICKET_LIFECYCLE_FACT[FCD_FAMILYNAME])

var top_fam = SWITCH(selected_top, "Top 5" , IF([Rank FTR] <=5, [FTR PERCENTAGE]) )

return top_fam

not sure if the above is corrected

Re: Top 10 with Multiple Columns

Ashish_Mathur — Thu, 27 Jul 2023 23:48:28 GMT

Hi,

Try this measure

Measure = calculate([FTR percentage],TOPN(5,all(TICKET_LIFECYCLE_FACT[FCD_FAMILYNAME]),[FTR percentage]),values(TICKET_LIFECYCLE_FACT[FCD_FAMILYNAME]))

To the visual, drag FCD Family name and this meausre.

Re: Top 10 with Multiple Columns

saud968 — Sat, 29 Jul 2023 07:33:57 GMT

Below is the table looking after using your DAX also, I tried to combine the RANK for two columns RANK FTR and Rank NOC Completed with DAX -

Combined Rank = (TICKET_LIFECYCLE_FACT[Rank FTR] + TICKET_LIFECYCLE_FACT[Rank NOC Completed]) / 2

Re: Top 10 with Multiple Columns

Ashish_Mathur — Sun, 30 Jul 2023 00:29:27 GMT

How do you expect me to help you without having access to the PBI file? Show the expected result there very clearly.

Re: Top 10 with Multiple Columns

saud968 — Mon, 31 Jul 2023 08:14:26 GMT

Hi Ashish_Mathur ,

I appreciate your help I am not sure if I can share the PBX file, however, I will try to see if I can get a sample data and submit the same in PBX file

Re: Top 10 with Multiple Columns

connect — Mon, 31 Jul 2023 10:27:07 GMT

Hi,
Please follow a 5 steps process:

1. Get the Rank of NOC Colum like this:

NOC_Rank =

RANKX(

ALL(TicketData),

TicketData[NOC Completed Rate (Within 24 hours)],

DESC,

Dense

)

2. Get the rank of FTR column:

FTR_Rank =

RANKX(

ALL(TicketData),

TicketData[FTR Percentage],

DESC,

Dense

)

3. Combined the ranks:

Combined_Rank = [NOC_Rank] + [FTR_Rank]

4. Get the final rank:

Final_Rank =

RANKX(

ALL(TicketData),

[Combined_Rank],

ASC,

Dense

)

5. Finally, you can get a new table as follows:

Top10Table =

FILTER(

ALL(TicketData),

[Final_Rank] <= 10

)

I hope this will solve your problem.

Re: Top 10 with Multiple Columns

saud968 — Wed, 02 Aug 2023 12:26:18 GMT

The filter one did not work rest all worked