Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter slicer using another filter

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 

AccountValue
A100
B150
C-50
D150

 

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.
 
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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!

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    BlockAccount
    Block1A
    Block1B
    Block2A
    Block2B
    Block2C
    Block3A
    Block3B
    Block3C
    Block3D