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
saud968
Responsive Resident
Responsive Resident

Top 10 with Multiple Columns

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". 

saud968_0-1690463057570.png

 

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

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

8 REPLIES 8
connect
Helper I
Helper I

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.

 







saud968
Responsive Resident
Responsive Resident

The filter one did not work rest all worked

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



saud968_0-1690615959218.png

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

saud968
Responsive Resident
Responsive Resident

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
 
saud968
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.