topic Re: Filter based on parameter (hiearchy) in DAX Commands and Tips

Filter based on parameter (hiearchy)

FrankBuild — Fri, 08 Nov 2024 08:20:22 GMT

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)

Re: Filter based on parameter (hiearchy)

saud968 — Fri, 08 Nov 2024 08:39:59 GMT

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!

Re: Filter based on parameter (hiearchy)

FrankBuild — Fri, 08 Nov 2024 09:25:28 GMT

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

Re: Filter based on parameter (hiearchy)

FrankBuild — Fri, 08 Nov 2024 10:31:33 GMT

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!

Re: Filter based on parameter (hiearchy)

saud968 — Fri, 08 Nov 2024 11:13:00 GMT

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]
)

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!