Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  ragnezza ,

 

Calculated tables are not used for dynamic filtering, you can create a slicer and measure that doesn't matter to affect the table visual.

 

Here are the steps you can follow：

1. Create calculated table.

Slicer_Table =
SUMMARIZE(
    'Table','Table'[Class],'Table'[Sub-Category])

2. Create measure.

Value A =
var _select=SELECTEDVALUE('Slicer_Table'[Class])
var _selectsub=SELECTCOLUMNS('Slicer_Table',"sub",'Slicer_Table'[Sub-Category])
return
IF(
    ISFILTERED('Slicer_Table'[Class])&&NOT(ISFILTERED('Slicer_Table'[Sub-Category])&&_select="A"),
    DIVIDE(
    SUMX(
        FILTER('Table',
        'Table'[Class]="A"),[Value XX]),2),
    SUMX(
        FILTER('Table',
        'Table'[Class]=_select&&'Table'[Sub-Category] in _selectsub),[Value XX])    
        )

 

Value B =
var _select=SELECTEDVALUE('Slicer_Table'[Class])
var _selectsub=SELECTCOLUMNS('Slicer_Table',"sub",'Slicer_Table'[Sub-Category])
return
IF(
    ISFILTERED('Slicer_Table'[Class])&&NOT(ISFILTERED('Slicer_Table'[Sub-Category])),
    DIVIDE(
    SUMX(
     'Table',
        [Value YY]),2),
    SUMX(
        FILTER('Table',
        'Table'[Class]=_select&&'Table'[Sub-Category] in _selectsub),[Value YY])    
        )

3. Result:

If only [Class] is selected, it is displayed according to your conditions:

If both slicers are selected, the result is realized based on the value of the slicer:

 

Best Regards,

Liu Yang

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

  • ragnezza's avatar
    ragnezza
    Frequent Visitor
    2 years ago

    Thanks Anonymous for spending time to look into this and for providing a solution, much appreciated!


    I'm not there yet, but I'm pretty close to it. These are my differet scenarios:

    A) NO FILTERS APPLIED
    Original view with no filters applied

    B) ONE FILTER APPLIED
    "Filter for Value A only" applied (it applies only to Value A, while Value B reamins unchanged)

    "Filter for Value B only" applied (it applies only to Value B, while Value A remains unchanged)

    C) BOTH FILTERS APPLIED WITH THE SAME VALUE
    This will filter out both Value A and Value B

    D) BOTH FILTERS APPLIED WITH DIFFERENT VALUES --> my challenge
    Different selections are made in "Filter for Value A only" and "Filter for Value B only" and because there are no records in common, the result is blank

    It looks like I have somehow to skip the level of hierarchy in the filters (Class and Sub-Category) and stick to the Category level, which is the highest level of the hierarchy.