Display Top 10 Ranked products with Slicer Selected and unselected

[alforc1]

I am looking for some assistance with the DAX I have written below.

Rank Top 10 Quantity = IF(NOT(ISBLANK([All Sales In Pack Quantity])),
                                        CALCULATE( [All Sales In Pack Quantity],
                                            FILTER( VALUES ( Lookup[Product Use Type] ),
                                                RANKX ( ALLSELECTED(Lookup),[All Sales In Pack Quantity], , DESC ) <=10)),BLANK())

This Dax is designed to show me the top 10 products sold in a category so when I select the Product use type in the slicer it then gives me a list of the top 10 selling products for the selected Product Use Type.  My DAX however falls over when no Product use type is selected.  When nothing is selected the above DAX gives me a full list of all products sold.

I need to adapt the above Dax to also give me a list of only the top 10 products sold when no slicer is selected. So if no slicer is selected it gives me the top 10 selling products across all product use types.

If anyone can offer a solution or if you need any further information please advise.

Thanks in advance for your help.

[v-cherch-msft]

Hi @alforc1

It seems you need to use ALL Function. It's better to share some simplified data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

Regards,

Cherie

[alforc1]

Hi Cherie,

Thank you for coming back to me with the suggestion to use all. I have tried this and it does give me the top 10 overall but then it doesnt interact with the slicer selection in the same way.  When I select a slicer type for example Liquids.  It only brings back the liquids that appear in the overall top 10. I would like to see the top 10 liquids not just the ones that appear in the overall top 10. I think I need a way to switch between the two following DAX. So for example if no selection is made perform DAX 1. Else perform DAX 2. 

I have attached some examples of the output using DAX 1.

DAX 1.Rank Top 10 Quantity = IF(NOT(ISBLANK([All Sales In Pack Quantity])),
                                        CALCULATE( [All Sales In Pack Quantity],
                                            FILTER( VALUES ( Lookup[Product Use Type] ),
                                                RANKX ( ALL(Lookup),[All Sales In Pack Quantity], , DESC ) <=10)),BLANK())

DAX 2.Rank Top 10 Quantity = IF(NOT(ISBLANK([All Sales In Pack Quantity])),
                                        CALCULATE( [All Sales In Pack Quantity],
                                            FILTER( VALUES ( Lookup[Product Use Type] ),
                                                RANKX ( ALLSELECTED(Lookup),[All Sales In Pack Quantity], , DESC ) <=10)),BLANK())

Thanks in advance

[v-cherch-msft · Accepted solution]

Hi @alforc1

You may try to use  HASONEVALUE Function. For example:

measure =
IF ( HASONEVALUE ( Lookup[Product Use Type] ), [DAX 2], [DAX 1] )

Regards,

Cherie

[alforc1]

That works perfectly, Thank you for taking the time to help me with this.

It is very much appreciated.