Forum Discussion
Filter based on parameter (hiearchy)
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:
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!
4 Replies
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!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 FrankTo 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!
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!
