Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need RANKX to exclude Blanks

Dear PBI enthusiasts!

I am relatively new to PowerBI and struggling through this one scenario. Use case - 

  1. I have a parameter (type of customer - Domestic / International / Contractors) and depending on what the user chooses, I need to use that as my column. There is a TotalSales calculated DAX which is UnitPrice*Qty. 
  2. Super simple:) However I need to show only top 5 AND exclude any blanks. 
  3. I got the Top5 using RankX, but not able to exclude blanks - tried many things, so would greatly apprciate your help. I have attached a sample PBXI file. (Hmm...not sure how to insert the test file...)

Thank you in advance!

<bottom> 

RANKX to exclude Blanks

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    In the RANKX function, don't use the whole table as the first argument but FILTER it by the cells/entries that don't have BLANKs. That's also simple. And if the cell is BLANK, just don't calculate the rank. Something like:

    [Ranking] = 
var AllItemsNonBlank = 
    filter(
        // Due to ALLSELECTED this is a relatve
        // ranking. If you want an absolute ranking,
        // use ALL( T[Item] ) instead.
        allselected( T[Item] ),
        not isblank( [your measure to rank with] )
    )
var Result =
    if( not isblank( [your measure to rank with] ),
        rankx(
            AllItemsNonBlank,
            [your measure to rank with]
        )
    )
return
    Result

     

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.