show hide columns based on slicer without using unpivot columns

[Anonymous]

I know we can hide/show columns based on slicer if  we unpviot tables as disscussed in this thread. https://community.powerbi.com/t5/Desktop/Hiding-column-based-on-slicer/m-p/297930#M131457

My question is: can we do it witout unpivoting tables? for example using DAX to create customerized column or measure

The problem of unpivoting methond is duplicating data.  If I have 5 value types,  the number of rows increase 5 times after unpivoting. 

The other issue is when result is represented in a line chart, if using unpivoted method, tooltip only shows one value, even if you have two values at the same timestamp. I  wish tooltip can displays all values from differet series at the same timestamp as shown below. 

[MFelix · Accepted solution]

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

[mac_oc]

@greatzt ,

Nice solution. I try to apply the same logic to a table where the request is to hide/show some columns (i.e. show only columns with $ or EUR) based on slicer. I create columns instead measure, but the SWITCH is not working. Could you extend the solution to show how to hide/disply columns on a table based on a slicer ?

Thank you in advance,

Mac

[Anonymous]

Hi MFelix,

Nice solution!

Is it also possible to do this in a table? If I change your chart into a table, the column is blank after making one selection, but I want to hide the column completely.

I hope you can help me with this one.

Thanks, Halmar

[MFelix]

Hi @Anonymous ,

Yes this option can also be used with a table or matrix visualization, if you use the matrix visualization you can use the single measure and the disconnected table on the columns if you are using a table you can use any of the two options.

Be aware that since you are using a table visualization depending on your calculation you may need to make some adjustments so the the totals ar e correct, if it's a simple sum no need but if there is anyt complex calculation that may be necessary.

[Anonymous]

Hi @MFelix ,

Ok, but I tried to do that with a normal table, but still the empty column is visible.

What am I missing?

Thanks, Halmar

[MFelix]

Hi @Anonymous ,

You are correct, sorry for the misseleading, in this case use the matrix visualization:

Just remember to turn off subtotals on columns

[v-piga-msft]

Hi @Anonymous ,

By my research and test, I'm afraid that Unpivot columns should be the most efficient way for that scenario. I did not find any way to achieve that by creating dax measure or calculated tables.

Hope @MFelix  has other ideas.

Best Regards,

Cherry

[Anonymous]

Thanks for your repsonse. If we can do dynamicly hide/show column without unpivoting table, it will save a lot of space.