Forum Discussion

FrankBuild's avatar
FrankBuild
Frequent Visitor
1 year ago
Solved

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 ...
  • saud968's avatar
    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!