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!
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 Frank
- saud9681 year agoMemorable Member
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!