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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AbdulAzad
Frequent Visitor

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

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

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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