show hide columns based on slicer without using unpivot columns

Hi Anonymous ,

 

Looking at what you ask I believe that the best way is making the unpivot of the column(s) however I can understand the issues you are pointing out, although the question about the tooltip I'm not abble to replicate, when I used the unpivot colum on my chart it gave the exact same result on the tooltip as having all the column on the chart. (this can happen due to my dataset only 2 columns were unpivot).

 

To what concerns the hiding and showing column on a table this is also possible using DAX however you need to create a measure that gets all the values from all the columns and a unrelated table with the name of the columns to use as your slicer.

 

Let's assume the following dataset:

 

Category	Quantity 1	Quantity 2
A	100	500
B	500	300
C	300	200
D	400	400

 

You need to follow the steps below:

• Create table:
  

  Slicer Selection

  Quantity 1

  Quantity 2

• Create the following measures (must be created one for each of the columns you want to show/hide):

• Quantity (1) =
  IF (
      CONTAINS (
          'Slicer Selection';
          'Slicer Selection'[Slicer Selection]; "Quantity 1"
      );
      SUM ( 'Base Table'[Quantity 1] );
      BLANK ()
  )
  
  Quantity (2) =
  IF (
      CONTAINS (
          'Slicer Selection';
          'Slicer Selection'[Slicer Selection]; "Quantity 2"
      );
      SUM ( 'Base Table'[Quantity 2] );
      BLANK ()
  )

• Add this two measure to you line chart and the final result is below:

 

You can also add additional parameter to check if they all are selected you don't get any result but is just additional adjusments.

 

In this case you are not duplicating the data but you are duplicating the measures to achieve the same result.

 

Another option to have only one measure is to create the following measure:

Quantity Selection =
SWITCH (
    TRUE ();
    CONTAINS (
        'Slicer Selection';
        'Slicer Selection'[Slicer Selection]; "Quantity 1"
    ); SUM ( 'Base Table'[Quantity 1] );
    CONTAINS (
        'Slicer Selection';
        'Slicer Selection'[Slicer Selection]; "Quantity 2"
    ); SUM ( 'Base Table'[Quantity 2] )
)

Then add the value from the Slicer selection table to your Legend. In this last case you need to add a SWITCH parameter for each column you want to replicate.

 

See both option on the PBIX file attach.

 

Regards,

MFelix