Filter based on parameter (hiearchy)

[FrankBuild]

Hello,

I have a matrix with 3 columns:
Organization hierarchy (3 levels) | OrgRanking | Actuals



I have a filter in the filter (on hierarchy level) pane which gives (correct) rank 1 to 25 (or whatever I'd like to give in):


Now I want to hide the filter pane for the end user and I want them to choose the amount of visible items (for example top 25 or top 50).
So I have to replace the filter above. My idea was to use a parameter table (5, 100, 5) and selectedparameter. I added the Values of the Parameter table to a slicer.. 

But I can't get the job done... Maybe I'm thinking to difficult or it is simply difficult 😉

This my first post, let me know if you need more info!

Thanks, Frank

The measure of OrgRanking is as follows:

OrgRanking =

VAR _OrgLevel2_Ranking =

SUMX(

    ADDCOLUMNS(

        SUMMARIZE(

            FinanceTransactions ,

            Organization[OrgPCLevel2] ) ,

    "@Level2 Ranking" ,

    RANKX( ALL(Organization[OrgPCLevel2]), [Actuals], , DESC ) ) ,

    [@Level2 Ranking] )

VAR _OrgLevel3_Ranking =

SUMX(

    ADDCOLUMNS(

        SUMMARIZE(

            FinanceTransactions ,

            Organization[OrgPCLevel3] ) ,

    "@Level3 Ranking" ,

    RANKX( ALL(Organization[OrgPCLevel3]), [Actuals], , DESC ) ) ,

    [@Level3 Ranking] )

VAR _OrgLevel4_Ranking =

SUMX(

    ADDCOLUMNS(

        SUMMARIZE(

            FinanceTransactions ,

            Organization[OrgPCLevel4] ) ,

    "@Level4 Ranking" ,

    RANKX( ALL(Organization[OrgPCLevel4]), [Actuals], , DESC ) ) ,

    [@Level4 Ranking] )

VAR _Results =

SWITCH( TRUE() ,

    ISINSCOPE(Organization[OrgPCLevel4] ) , _OrgLevel4_Ranking ,

    ISINSCOPE(Organization[OrgPCLevel3] ) , _OrgLevel3_Ranking ,

    ISINSCOPE(Organization[OrgPCLevel2] ) , _OrgLevel2_Ranking ,

BLANK() )

RETURN

_Results    

Measure of selectedparemeter:

SelectedParameter = SELECTEDVALUE(ParameterTabel[Value], 25)

[FrankBuild]

Problem is solved! 😊

I kept the filter below and set the Bottom to the max of parametertable (value) which is 100 in this case.

Thank you so much!

[saud968 · Accepted solution]

Here’s a step-by-step approach to achieve your goal:

Create a Parameter Table:
Create a table with the values you want to use for filtering (e.g., 5, 10, 15, …, 100).
Name this table ParameterTable.
Add the Parameter Table to a Slicer:
Add the ParameterTable to your report and create a slicer visual from it.
Create a Measure for the Selected Parameter:
You already have this measure:
SelectedParameter = SELECTEDVALUE(ParameterTable[Value], 25)

Modify the OrgRanking Measure:
Update your OrgRanking measure to use the selected parameter value. Here’s an example of how you can modify it:
OrgRanking =
VAR _OrgLevel2_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel2]
),
"@Level2 Ranking",
RANKX(ALL(Organization[OrgPCLevel2]), [Actuals], , DESC)
),
[@Level2 Ranking]
)

VAR _OrgLevel3_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel3]
),
"@Level3 Ranking",
RANKX(ALL(Organization[OrgPCLevel3]), [Actuals], , DESC)
),
[@Level3 Ranking]
)

VAR _OrgLevel4_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel4]
),
"@Level4 Ranking",
RANKX(ALL(Organization[OrgPCLevel4]), [Actuals], , DESC)
),
[@Level4 Ranking]
)

VAR _Results =
SWITCH(
TRUE(),
ISINSCOPE(Organization[OrgPCLevel4]), _OrgLevel4_Ranking,
ISINSCOPE(Organization[OrgPCLevel3]), _OrgLevel3_Ranking,
ISINSCOPE(Organization[OrgPCLevel2]), _OrgLevel2_Ranking,
BLANK()
)

RETURN
IF(_Results <= [SelectedParameter], _Results, BLANK())

Apply the Measure to Your Visual:
Use the modified OrgRanking measure in your matrix visual. This will ensure that only the top N items, based on the selected parameter, are displayed.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[FrankBuild]

Thank you Saud! 
 
The parameter (table) & slicer do work and I also see the right amount of rankings. But the organizations without a rank are also visible. I only want to see 5 organizations in this example:
 


Regards Frank

[saud968]

To ensure that only the top-ranked organizations are visible and those without a rank are hidden, you can modify your measure to return BLANK() for organizations that fall outside the selected top N. Here’s how you can adjust your OrgRanking measure:

Modify the OrgRanking Measure:
Update the measure to return BLANK() for organizations that do not fall within the top N ranks.
OrgRanking =
VAR _OrgLevel2_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel2]
),
"@Level2 Ranking",
RANKX(ALL(Organization[OrgPCLevel2]), [Actuals], , DESC)
),
[@Level2 Ranking]
)

VAR _OrgLevel3_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel3]
),
"@Level3 Ranking",
RANKX(ALL(Organization[OrgPCLevel3]), [Actuals], , DESC)
),
[@Level3 Ranking]
)

VAR _OrgLevel4_Ranking =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
FinanceTransactions,
Organization[OrgPCLevel4]
),
"@Level4 Ranking",
RANKX(ALL(Organization[OrgPCLevel4]), [Actuals], , DESC)
),
[@Level4 Ranking]
)

VAR _Results =
SWITCH(
TRUE(),
ISINSCOPE(Organization[OrgPCLevel4]), _OrgLevel4_Ranking,
ISINSCOPE(Organization[OrgPCLevel3]), _OrgLevel3_Ranking,
ISINSCOPE(Organization[OrgPCLevel2]), _OrgLevel2_Ranking,
BLANK()
)

RETURN
IF(_Results <= [SelectedParameter], _Results, BLANK())

Filter the Visual:
Ensure your matrix visual is set to filter out BLANK() values. You can do this by applying a visual-level filter to exclude BLANK() values for the OrgRanking measure.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!