Blank Values when multiple filters are applied

[ragnezza]

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

[v-yangliu-msft]

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]

Thanks @v-yangliu-msft 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.