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

[AbdulAzad]

Hi, 

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.

https://community.powerbi.com/t5/Desktop/Dynamic-Category-Using-a-Slicer/m-p/216971#M96101

https://community.powerbi.com/t5/Desktop/Dynamically-Add-or-Remove-Dimension-or-attributes-to-a-tabl... 

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

[V-pazhen-msft]

@AbdulAzad 

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

[AbdulAzad]

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

https://community.powerbi.com/t5/Desktop/Dynamically-Add-or-Remove-Dimension-or-attributes-to-a-tabl...

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,

Abdul