Filter one table top 10 rows by value based on slicer

[tim_m15]

Hello dear community,

First I explain my starting position and then the goal I have.

I have created a new table in the dataset (screenshot 1 at the bottom of this post), which will serve me as a slicer in the report in single select selection controls with the function to dynamically filter my one and only table depending on the selection (screenshot 2 at the bottom of this post).

Furthermore there is a Measure "Filter Dashboard" (Code-Example below), which selects each of the Values (Whole, Top 10, Bottom 10) with a SELECTEDVALUE-Function. I put this measure as a filter on the one and only table in the report and say "Filter Dashboard is 1" to make it active.

You can see my approach how I created the "Filter Dashboard" measure in this Code-Example:

Filter Dashboard = 

IF (
    SELECTEDVALUE ('Filter-Table'[Value] ) = "Whole",
    1,

IF (
    SELECTEDVALUE ( 'Filter-Table'[Value]) = "Top 10",
    TOPN(10,'Bookings-Table',[Turnover-Measure],DESC)

IF (
    SELECTEDVALUE ( 'Filter-Table'[Value]) = "Bottom 10",
   TOPN(10,'Bookings-Table',[Turnover-Measure],ASC)
))))

My goal is to filter my table only in the two cases top 10 or bottom 10 that I select in single select selection controls in my Slicer. 

But unfortunately it does not work, what I am doing wrong?

Thank you for your support, I look forward to your answers.
Best Regards
Tim

Screenshot 1Screenshot 2

[MFelix · Accepted solution]

Hi @tim_m15 ,

Your coding seems ok, however believe that the best approach is to use the RANKX measure.

I would use a combination between switch and the rankx see below:

Filtering =
VAR Ranking_Asc =
    RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( [Total value] ),, ASC )
VAR Ranking_Desc =
    RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( [Total value] ),, DESC )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Filter-Table'[ID] ),
        1, 1,
        2, IF ( Ranking_Asc <= 10, 1 ),
        3, IF ( Ranking_Desc <= 10, 1 )
    )

Now just filter all 1 on your visualization:

Be aware the since my table is small I'm using the entire table on the ALLSELECTED you may need to adjust this to just some columns or to a summarized version of your table in order to have the expected result.

This is just one alternative there are others that can use TOPN, but belive this can make what you need.

Check PBIX file attach,

[tim_m15]

Hi Miguel, 

thank you for your detailed answer and for the sample file. Your solutions works fine!