Forum Discussion
Slicer using filtered Hierarchy
- Anonymous1 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 creationTable 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 outputBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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
Thanks! I have added this in and it worked well. I get a (Blank) in the hierarchy dropdown if the sub categories dont add up to ten individually but there are enough that together they add up to 10. But that works for me.
Cheers!