Need a table visual to show opposite of what is selected across multiple slicers

[Uthraa92]

Hi all,

I have been working on PBI report and I have got a situation here.

Say I have the below table (10 records):

Table A
ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
1	A	10	5	2	STJS
2	B	20	3	4	STJS
3	C	30	6	6	STJS
4	D	40	1	7	Clear
5	E	50	9	4	Clear
6	F	60	6	7	STJS
7	G	70	1	1	STJS
8	D	80	5	3	STJS
9	E	90	3	7	Clear
10	A	50	1	5	STJS

Now I will apply page level filter for only STJS entries. So, we will get the below table,

 Table B (7 records):

ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
1	A	10	5	2	STJS
2	B	20	3	4	STJS
3	C	30	6	6	STJS
6	F	60	6	7	STJS
7	G	70	1	1	STJS
8	D	80	5	3	STJS
10	A	50	1	5	STJS

Now I have Amount Slicer say, between 10 to 30. The records from Table B will get split into 2 table visuals based on the Amount slicers. So now, one table visual will show what was selected (first three records 10 - 30) say Table 1 and remaining will show in another table visual say Table 2 (as shown below).

Table 1 (3 records):

ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
1	A	10	5	2	STJS
2	B	20	3	4	STJS
3	C	30	6	6	STJS

Table 2 (4 records):

ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
6	F	60	6	7	STJS
7	G	70	1	1	STJS
8	D	80	5	3	STJS
10	A	50	1	5	STJS

Now, I have other slicers say 1) Desc - A and 2) ID - 6 to be applied on Table 2, So Table 2 will show 2 records and another table say Table 3 will show remaining records (as shown below)

Table 2 (2 records – once the filter on slicers Desc and ID are applied)

ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
6	F	60	6	7	STJS
10	A	50	1	5	STJS

Table 3 (Remaining 2 records):

ID	Desc	Amount	Period 1 Amount	Period 2 Amount	Category
7	G	70	1	1	STJS
8	D	80	5	3	STJS

This Table 3 values (mainly ID) will be considered for other pages:

Also, for every table visual, I will have to show record count and Amount Total. Can anyone help how to achieve this?

Thanks in advance.

[Anonymous]

Hi @Uthraa92 ,

First of all, I need to clarify that Power BI is not able to split a visual object into 3 visual objects, you can only create 3 visual objects and set them up individually.

Please try this way:
Here is my sample data:

Use these DAXs to create several calculated tables for slicer:

ID = VALUES('Table A'[ID])

Desc = VALUES('Table A'[Desc])

Amount = SELECTCOLUMNS('Table A', 'Table A'[Amount])

Create three table visuals:

Then use these DAXs to create another three measures as filters:

Table_1 = 
IF(
    MAX('Table A'[Amount]) IN VALUES(Amount[Table A_Amount]),
    1,
    0
)

Table_2 = 
IF(
    MAX('Table A'[Amount]) IN VALUES(Amount[Table A_Amount]),
    0,
    IF(
        MAX('Table A'[ID]) IN VALUES('ID'[ID]) || MAX('Table A'[Desc]) IN VALUES('Desc'[Desc]),
        1,
        0
    )
)

Table_3 = 
IF(
    MAX('Table A'[Amount]) IN VALUES(Amount[Table A_Amount]),
    0,
    IF(
        MAX('Table A'[ID]) IN VALUES('ID'[ID]) || MAX('Table A'[Desc]) IN VALUES('Desc'[Desc]),
        0,
        1
    )
)

As shown in the following figure to set up filter (I use the visual object Table 1 as an example, Table 2 and Table 3 set up the same as Table 1😞

The same as Table 2 and Table 3:

And the final output is as below:

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

[Uthraa92]

Hi, Thanks for the detailed steps. For you comment " I need to clarify that Power BI is not able to split a visual object into 3 visual objects, you can only create 3 visual objects and set them up individually", I am totally on the same page and meant the same. 

I had used the same logic for filtering the amount first. I have two concerns here:

1. The filters are independent to each other. When I filter the Amount, it should impact the other filters ie., ID and Desc. Say if I say Amount between 10 and 30 then the filters for ID and Desc should show only remaining values. Similarly once I filter ID, it should impact Desc.

2. Even when there is one filter applied (ID or Desc), I need to get the table visuals filtered. Now, only when I select values across ID and Desc, the tables 2 and 3 are getting highlighted. Can you tell if I have mistaken something ?

Thanks in advance.

[Anonymous]

Hi @Uthraa92 ,

I am a little confused.
You said that filters should be able to affect each other, but in your original requirement, you mentioned filtering Desc = A and ID = 6 at the same time, and the IDs corresponding to A in the data you provided are 1 and 10. If you want to achieve mutual influence between filters, then after you select Desc = A, you can no longer select ID = 6, because the slicer of ID will only have two values ​​left, 1 and 10. Similarly, after you select ID = 6, you can no longer select Desc = A, because at this time, there is only one value F left in the slicer of Desc.

Please confirm your needs again, thank you!

Best Regards,
Dino Tao

[Anonymous · Accepted solution]

Hi @Uthraa92 ,

You can try this:
Use these DAXs to create three calculated tables for slicers:

Amount = SUMMARIZE('Table',[Amount])

Desc = SUMMARIZE('Table',[ID],[Desc])

ID = SUMMARIZE('Table',[ID],[Desc])

Create slicers using the Amount column from the Amount table, the ID column from the ID table, and the Desc column from the Desc table, then use these DAXs to create two measures as filters to realize slicers affect each other:

Measure 2 = 
var a=
CALCULATETABLE (
    VALUES ( 'Table'[ID] ),
    'Table'[Amount] IN VALUES ( 'Amount'[Amount] )
)
var b=
IF (
    ISFILTERED ( 'ID'[ID] ),
    COUNTROWS ( FILTER ( 'Desc', [ID] IN VALUES ( 'ID'[ID] ) ) )
)
return IF(not(SELECTEDVALUE('Desc'[ID]) in a)&&b=0,1,0)

Measure 3 = 
var a=
CALCULATETABLE (
    VALUES ( 'Table'[ID] ),
    'Table'[Amount] IN VALUES ( 'Amount'[Amount] )
)
var b=
IF (
    ISFILTERED ( 'Desc'[Desc] ),
    COUNTROWS ( FILTER ( 'ID', [ID] IN VALUES ( 'Desc'[ID] ) ) )
)
return IF(not(SELECTEDVALUE('ID'[ID]) in a)&&b=0,1,0)

Set Filters on this visual up as shown below:

Then use these DAXs to create three measures to create the table visuals:

Measure 1 = CALCULATE(SUM('Table'[Amount]),'Table'[Amount] in VALUES('Amount'[Amount]))

Measure 4 = 
var a=CALCULATETABLE(VALUES('Table'[ID]),'Table'[Amount] in VALUES('Amount'[Amount]))
return 
IF (
    NOT ( SELECTEDVALUE ( 'Table'[ID] ) IN a ),
    SWITCH (
        TRUE (),
        ISFILTERED ( 'ID'[ID] )
            && ISFILTERED ( 'Desc'[Desc] ) = FALSE (), CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[ID] IN VALUES ( 'ID'[ID] ) ),
        ISFILTERED ( 'Desc'[Desc] )
            && ISFILTERED ( 'ID'[ID] ) = FALSE (), CALCULATE ( SUM ( 'Table'[Amount] ), 'Table'[Desc] IN VALUES ( 'Desc'[Desc] ) ),
        ISFILTERED ( 'ID'[ID] ) && ISFILTERED ( 'Desc'[Desc] ),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                OR (
                    'Table'[Desc] IN VALUES ( 'Desc'[Desc] ),
                    'Table'[ID] IN VALUES ( 'ID'[ID] )
                )
            ),
        CALCULATE ( SUM ( 'Table'[Amount] ), NOT ( 'Table'[ID] IN a ) )
    )
)

Measure 5 = 
var a=CALCULATETABLE(VALUES('Table'[ID]),'Table'[Amount] in VALUES('Amount'[Amount]))
return 
IF (
    NOT ( SELECTEDVALUE ( 'Table'[ID] ) IN a ),
    SWITCH (
        TRUE (),
        ISFILTERED ( 'ID'[ID] )
            && ISFILTERED ( 'Desc'[Desc] ) = FALSE (),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                NOT ( 'Table'[ID] IN VALUES ( 'ID'[ID] ) )
            ),
        ISFILTERED ( 'Desc'[Desc] )
            && ISFILTERED ( 'ID'[ID] ) = FALSE (),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                NOT ( 'Table'[Desc] IN VALUES ( 'Desc'[Desc] ) )
            ),
        ISFILTERED ( 'ID'[ID] ) && ISFILTERED ( 'Desc'[Desc] ),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                NOT (
                    OR (
                        'Table'[Desc] IN VALUES ( 'Desc'[Desc] ),
                        'Table'[ID] IN VALUES ( 'ID'[ID] )
                    )
                )
            )
    )
)

Put Measure 1 into the Table 1, Measure 4 into the Table 2 and Measure 5 into the Table 3:

And the final output is as below:
Only slicer Amount:

Slicer Amount + slicer ID:

Slicer Amount + slicer Desc:

Slicer Amount + slicer ID + slicer Desc:

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

[Uthraa92]

Hi @Anonymous,

Thank you so much for taking time and effort into providing me with the steps. As shown in your screenshot, every selection on a slicer should impact the other slicer and show only the remaining values. Will try your method, thanks again 🙂

[JB_Gold]

I don't have any specific code t offer, but my first inclination after reading your post would be to set up measures to get the selected value of the slicers useing the DAX formula HASONEVALUE.

Then after you have all the measures make one more measure that stores a variable for each of the HASONEVALUE measures and do a calculatetable variable on the values of the primary column using NOT() with all the HASONEVALUE variables.

Then you can take the calculatetable  and do a Boolean IF stament as the return value like the below example

IF(<ID from table> IN calculatetableVariable, 1,0)

Then you should be able to use the newlycreated if measure as a visual filter on table 3 and set the values to be 1 in the visual filter. You would then have to remove interactions from table 3 and the other slicers.

Disclaimer: when writing this, I can't remember if removing interactions messes with the visual filters, but if not this general idea should get you started.

If you have questions let me know, but these were just my general thoughts on how to approach it.

[Uthraa92]

Hi, Thanks for providing me a logic. I am new to using DAX expressions, but I did try using HASONEVALUE function but the filtering doesnt work well for me.