topic Re: Blank Values when multiple filters are applied in DAX Commands and Tips

Blank Values when multiple filters are applied

ragnezza — Thu, 25 Apr 2024 21:59:50 GMT

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

Re: Blank Values when multiple filters are applied

Anonymous — Fri, 26 Apr 2024 03:11:49 GMT

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

Re: Blank Values when multiple filters are applied

ragnezza — Fri, 26 Apr 2024 17:05:47 GMT

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.