How to combine numbers and the word "ALL" in a filter?

[Mastema]

Hi guys,

I wonder if it was possible to combine numbers and a word (namely "All") in a filter?

At this point I have a table built explicitely for a Filter (it has values as follows: 5, 10, 25, 50, 100, 500), as I need it to adjust the report for the Top N (a number chosen in a Filter) Firms. 

Still, as my formula goes as follows: SelectedTopNNumber = MAX('TopN Options'[TopN]),

it won't show the result for all the Firms(700+ and counting), but for 500 Top Firms.

To manually change the maximum value in a Filter is not an option, as it takes too much time and is not efficient 

("All" should refer to the VIEW_KOMPONENTE_UMSATZ'[VM_Firma])

Would be grateful if you'll help me solve the problem 

[v-yulgu-msft]

Hi @Mastema,

I suppose that you have a [Rank] measure and a [check] measure similar to below. And you added [check] measure to visual level filter so that visual always returns TopN records.

check = IF([Rank]<=[SelectedTopNNumber],1,0)

In your scenario, to make the visual to show all the Firms(700+ and counting) when there is no selection in slicer, please modify the [check] measure to:

check = IF(ISFILTERED('TopN Options'[TopN]), IF([Rank]<=SelectedTopNNumber,1,0),1)

Best regards,

Yuliana Gu

[Mastema]

@v-yulgu-msft Thanks for the answer!

Yes, I was using the [check] measure similar to the one you proposed 🙂

Still, the issue is that I can't make the graphs show the overall amount, if there is no measure chosen in the Filter (as there is a max of 500 in the TopN Filter)

I have the following steps for Filtering:

Total Premium Booked= SUM('acc VIEW_KOMPONENTE_UMSATZ'[Premium])

Rank = RANKX(All('acc VIEW_KOMPONENTE_UMSATZ'[VM_Firma]);[Total Premium Booked];;DESC)

SelectedTopNNumber = MAX('TopN Options'[TopN])

Ar = IF([Rank]<=[SelectedTopNNumber];"=>";"") - my analogue of the check measure

aVol 2 Premium for TopN = calculate([Total Premium Booked];filter(values('acc VIEW_KOMPONENTE_UMSATZ'[VM_Firma]);if(rankx(all('acc VIEW_KOMPONENTE_UMSATZ'[VM_Firma]);[Total Premium Booked];;DESC)<=[SelectedTopNNumber];[Total Premium Booked];blank())))

The last formula is the one I insert in the graph to see the results for TopN (chosen in the Filter) Firms. Still, if I don't choose anything in the Filter, I will get the result for Top 500 firms (as it is the Max number in the Filter), and I need the overall result 😞

[v-yulgu-msft]

Hi @Mastema,

There is no need to write a measure like this:

aVol 2 Premium for TopN = calculate([Total Premium Booked];filter(values('acc VIEW_KOMPONENTE_UMSATZ'[VM_Firma]);if(rankx(all('acc VIEW_KOMPONENTE_UMSATZ'[VM_Firma]);[Total Premium Booked];;DESC)<=[SelectedTopNNumber];[Total Premium Booked];blank())))

You just need to add measure [Total Premium Booked] into graph. Then, add the [check] measure that I posted above into visual level filter and set its value to 1.

check = IF(ISFILTERED('TopN Options'[TopN]), IF([Rank]<=SelectedTopNNumber,1,0),1)

Best regards,

Yuliana Gu

[Mastema]

Thank you @v-yulgu-msft!

Unfortunately doesn't still work as I want it to

Maybe I've complicated the additional formulas I have too much

Will try to resolve the problem myself based on your suggestions

[v-yulgu-msft]

Hi @Mastema,

Have you worked it out? If you have resolved it by yourself, would you please kindly share the valid resolution so that it can benefit more users?

Regards,

Yuliana Gu