cancel
Showing results for 
Search instead for 
Did you mean: 
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

3 REPLIES 3
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

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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!