Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
greatzt
Helper II
Helper II

show hide columns based on slicer without using unpivot columns

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. 

tooltip.PNG

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @greatzt ,

 

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:

Selection.gif

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @greatzt ,

 

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:

Selection.gif

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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
Not applicable

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix ,

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

HalmarHeijnen_0-1652262544245.png

 

What am I missing?

 

Thanks, Halmar

 

Hi @Anonymous ,

 

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

MFelix_0-1652266484952.png

 

Just remember to turn off subtotals on columns


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @greatzt ,

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.