Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Hide and display columns based on a slicer, which is based on derived table (UNION).



I wanted to show and hide columns in a table/matrix visual based on slicer selection, columns are like Effective_Date, Purchase_Date, Quantity, Order_Number, which is based on a derived table (UNION table). 


I have seen solutions which are based on single fact table, on which by using pivoting we can perform hide and unhide of columns in table or matrix visual. Here are I am using tables in import mode. 


But how can we achieve the same on a derived table (UNION table) on which we have joins with other tables.


Your help on this will be much appreciated.

Thanks & Regards, A

Community Support
Community Support


I guess you could show selected column by create identical measures. Can you share sample data including the union table so we can test out?


Paul Zheng _ Community Support Team

Hi Paul,


I am using matrix visualization, where trying to display item, location, scenario columns in rows section and Effective_Date, Purchase_Date, Quantity, Order_Number columns in columns section and its values in values section based on the slicer selection as I have pivoted the data. 


I followed the below link to do pivoting



This is my data in the table


Item Location Scenario Order Number Effective Date Purchase Date Quantity
A AB Live 1 1-Mar-15 7-Mar-15 30
A AB Live 2 2-Mar-15 10-Mar-15 20
A AB Live 3 3-Mar-15 13-Mar-15 20

My slicer has Order Number, Effective Date, Purchase Date and Quantity multi select check box. Based on the selection of the columns, I should get the similar to the table output as provided above in the matrix visualization. Whereas Quantity column if we sums up come as 70, but after pivoting I am not getting the Quantity data as 70. Which is not as expected, I don't know where the data is going. Irrespective of any selection of columns like Order Number,  Effective Date, Purchase Date and Quantity. I should get the data similar to my table data in the database. But I am not getting the expected data as in the table.


I am doing this pivoting of data on a single fact table before doing union, I have two base tables, where I have to do union, actually on the union table I have to peform pivoting, whereas if I individually pivot data on the two base tables and perform union, I don't think it is the correct way, in this situation what is the correct approach. If we keep union stuff aside, for my current situation with single fact table, i am not getting the expected output?


I have a question, does the pivoting of data will only support, if we display the data in rows section instead of displaying in columns section?


Can you please let me know, what is the going wrong or my requirement is not achievable in Power BI?


Thanks & Regards,


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors