Forum Discussion
Filter based on parameter (hiearchy)
- 1 year ago
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!
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!