Filter slicer using another filter

[aerizk]

Hello 

I am trying to implement a logic to use a slicer to apply a filter on another slider. For the sake of simplicity I will put a simple version of my use case. 

I have a table with a value for each account with the following format 

Account	Value
A	100
B	150
C	-50
D	150

So I have four measures of 

A = CALCULATE( SUM( Table[Value] ), Table[Account] = "A" )

B = CALCULATE( SUM( Table[Value] ), Table[Account] = "B" )

C = CALCULATE( SUM( Table[Value] ), Table[Account] = "C" )

D = CALCULATE( SUM( Table[Value] ), Table[Account] = "D" )

First I want to create a new account without increasing my data model size or putting any extra rows so I created a new account F with a measure that is a calculation of: 

E = [D] - ([A] + [B] + [C]) 

For me to be able to use this new account as a new "dimension", I created a disconnected table with my 5 values (A, B, C, D, E) and a switch measure to reflect the value of each so that I can use that new table to slice, along with the switch measure which will represent my value. 

NEWValue =

  VAR __Account = MAX( NewDim[Account] )

  VAR __Result =

    SWITCH( __Account ,

      "A", [A],

      "B", [B],

      "C", [C],

      "D", [D],

      "E", [E],

    )

RETURN

  __Result

So using the new slicer if I selected any of the dimension variables from A to E everything works fine. (Imagine a stacked bar chart)

Now imagine I have another hierarchical arrangement where

Block1 = [A] + [B]

Block2 = [A] + [B] + [C] + [D] 

Block3 = [A] + [B] + [C] + [D] + [E]

What I am looking to do is to have a new slicer from which I can select Block1, Block2, or Block3 and this would propagate to filter for either [A] + [B] or [A] + [B] + [C] + [D] or [A] + [B] + [C] + [D] + [E]

My issue is that each block can contain either all the accounts or just a few so I am not sure how to do this. I tried creating another disconnected table with my 3 Blocks but couldn't figure out what to do after. 

Thanks a lot.

[lbendlin · Accepted solution]

Let your data model do the work for you.  Have a table with all blocks and wire it in. Then feed your slicer from that table.

[aerizk]

Thanks @v-huijiey-msft @lbendlin, this made it much easier. I did it slightly different within the same disconnected table which works similarly. Thanks a lot!

Block	Account
Block1	A
Block1	B
Block2	A
Block2	B
Block2	C
Block3	A
Block3	B
Block3	C
Block3	D

[v-huijiey-msft · Accepted solution]

Hi @aerizk ,

Thanks for the reply from lbendlin .

Create a disconnected table that should have one column, let is call it NewDim2, with three rows: Block1, Block2, and Block3.

Creates a measure that calculates the total value of each block based on the selection.

BlockValue=
VAR SelectedBlock = SELECTEDVALUE( 'NewDim2'[Block] )
RETURN
     SWITCH(
         SelectedBlock,
         "Block1", [A] + [B],
         "Block2", [A] + [B] + [C] + [D],
         "Block3", [A] + [B] + [C] + [D] + [E],
         BLANK()
     )

Place the Block column in the Slicer and the BlockValue in the card visual object. The final page effect is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin · Accepted solution]

Let your data model do the work for you.  Have a table with all blocks and wire it in. Then feed your slicer from that table.