Blank Values when multiple filters are applied

Question

Hello - I have a simple dataset that looks like this:

Category	Sub-Category	Class	Value XX	Value YY
AAA	X1	A	1000	2000
AAA	X2	A	400	2500
AAA	X3	B	2000	300
BBB	X2	A	5000	1000
CCC	X1	B	150	1200
CCC	X2	B	350	10000
CCC	X3	A	300	800

Then I have a summary table that looks like this:

Category	Value A (=Value XX/2)	Value B(=Value YY/2)
AAA	1700	2400
BBB	2500	500
CCC	400	6000

Then I have two filters, one for Value A (Filter A) and one for Value B (Filter B), that are the result of the concatenation of:

Filter A = "Class" + "Sub-Category" (ie. A-X1, A-X2, B-X3,...)

Filter B = "Class_duplicate" + "Sub-Category_duplicate" (ie. A-X1, A-X2, B-X3,...) --> I created a duplicate field for both "Class" and "Sub-Category" to avoid interference between the two filters

One of my goals was to apply Filter A only to Value A (with Value B not impacted by any selection) and Filter B to apply only to Value B (with Value A not impacted by any selection). I was able to accomplish this with the following measures:

Value A = CALCULATE( DIVIDE(Value XX,2), ALL(Table_name, Class, Sub-Category) )

Value B = CALCULATE( DIVIDE(Value YY,2), ALL(Table_name, Class_duplicate, Sub-Category_duplicate) )

This works only if:

I apply one Filter at a time OR
if the selected combination of "Class"+"Sub-Category" from both filters is the same

But considering that my summary table is at the "Category" level, while my filters are at the "Class"+"Sub-Category" level, the PROBLEM is if:

I make a selection from both filters AND
the combination of "Class"+"Sub-Category" is different between the two filters, which will return blank values in my summary table for both Value A and Value B.

I tried combining different functions inside the measures Value A and Value B, but with no success.

I hope I made it clear to understand, any recommendation/hint will be greatly appreciated.

Thanks!

Davide

anonymous · Answer

Hi&nbsp; ragnezza&nbsp;,
&nbsp;
Calculated tables are not used for dynamic filtering, you can create a slicer and measure that doesn't matter to affect the table visual.
&nbsp;
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])&amp;&amp;NOT(ISFILTERED('Slicer_Table'[Sub-Category])&amp;&amp;_select="A"),
    DIVIDE(
    SUMX(
        FILTER('Table',
        'Table'[Class]="A"),[Value XX]),2),
    SUMX(
        FILTER('Table',
        'Table'[Class]=_select&amp;&amp;'Table'[Sub-Category] in _selectsub),[Value XX])    
        )
&nbsp;
Value B =
var _select=SELECTEDVALUE('Slicer_Table'[Class])
var _selectsub=SELECTCOLUMNS('Slicer_Table',"sub",'Slicer_Table'[Sub-Category])
return
IF(
    ISFILTERED('Slicer_Table'[Class])&amp;&amp;NOT(ISFILTERED('Slicer_Table'[Sub-Category])),
    DIVIDE(
    SUMX(
     'Table',
        [Value YY]),2),
    SUMX(
        FILTER('Table',
        'Table'[Class]=_select&amp;&amp;'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:

&nbsp;
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 · Answer

Thanks&nbsp;Anonymous&nbsp;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 APPLIEDOriginal view with no filters appliedB) 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 VALUEThis will filter out both Value A and Value BD) BOTH FILTERS APPLIED WITH DIFFERENT VALUES --&gt; my challengeDifferent 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 blankIt 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.&nbsp;