Forum Discussion

Ankh-Morpork's avatar
Ankh-Morpork
Frequent Visitor
1 year ago
Solved

Slicer using filtered Hierarchy

Hi, I have some Survey data in which  i have Organisational Levels 1 to 4. When responding to the survey respondents would select where they are on the organisation levels and their response would be...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Ankh-Morpork ,
    According to your description, you can either create a new table or create columns on the original table following the logic of this table creation

    Table 2 = 
    SUMMARIZE(
        'Table',
        'Table'[Lvl 1],
        'Table'[Lvl 2],
        'Table'[Lvl 3],
        'Table'[Lvl 4],
        "Level 1",
        IF(
            CALCULATE(
                SUM('Table'[Responses]),
                ALLEXCEPT(
                    'Table',
                    'Table'[Lvl 1]
                )
            ) >= 10,
            'Table'[Lvl 1],
            BLANK()
        ),
        "Leve 2",
        IF(
            CALCULATE(
                SUM('Table'[Responses]),
                ALLEXCEPT(
                    'Table',
                    'Table'[Lvl 1],
                    'Table'[Lvl 2]
                ) 
            )>= 10,
            'Table'[Lvl 2],
            BLANK()
        ),
        "Level 3",
        IF(
            CALCULATE(
                SUM('Table'[Responses]),
                ALLEXCEPT(
                    'Table',
                    'Table'[Lvl 1],
                    'Table'[Lvl 2],
                    'Table'[Lvl 3]
                ) 
            )>= 10,
            'Table'[Lvl 3],
            BLANK()
        ),
        "Level 4",
        IF(
            CALCULATE(
                SUM('Table'[Responses]),
                ALLEXCEPT(
                    'Table',
                    'Table'[Lvl 1],
                    'Table'[Lvl 2],
                    'Table'[Lvl 3],
                    'Table'[Lvl 4]
                ) 
            )>= 10,
            'Table'[Lvl 4],
            BLANK()
        )
    )

    Setting is not blank in level 3 page filters
    Final output

    Best regards,
    Albert He


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly