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
Babette
Helper III
Helper III

Select dynamic fields to display in Table visual

Hi,

The Import query contains almost 50 fields - mostly Text fields (location - region, address, building etc.; machine - type, cofig etc.) and no Measures.

The purpose of this report is to allow end users to export data into csv file.

Is it possible to have a pick list (slicer) of the 30-40 fields the users can select to display in the visual Table so they can export to csv what they need?

Thank you so much in advance.

1 ACCEPTED SOLUTION
belvoir99
Resolver I
Resolver I

@Babette oh I see 🤔 Sorry I don't have a solution, though here are some approaches I've considered. Can anyone else out there help? Maybe I've missed something obvious? 


You have to keep the fact table in the original format - so you will need to do something completely different. This is because slicers work on a single column and the slicer values are the values from that column.

Out of the box, a matrix puts the values from a single column as column headers and populates from there. So you can't use that. A table allows you to put the columns across the top but there's no way to filter them.

 

I did think about using a solution called 'disconnected tables'. A good example is this: https://p3adaptive.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/ 

However, this produces a slicer which will work on the rows, when you want a slicer on the columns. In other words, you want users to filter the columns but keep the original format.

The only other solution I thought is to use Calculation Groups. You end up with something similar to a slicer table which you put in the Columns section of the Matrix visual. It will then appear in the Filters pane (filters on this visual) as 'Name' and your users can then select the columns they want, and then export that. However, the export file unpivots the data back to the format we don't want! 

Sorry I couldn't help any further. 

 

View solution in original post

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

Hi @Babette ,

 

Could you tell me the post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.Hope to hear from you‌‌😀

 

Best Regards,
Eyelyn Qin

Babette
Helper III
Helper III

Thank you @belvoir99 and @v-eqin-msft.  Very much appreciate your help.  My client has agreed to do without dynamic fields select.  I got to learn an interesting method to add dynamic field which i can put to use should there be a need in future reports.

v-eqin-msft
Community Support
Community Support

Hi @Babette ,

 

According to the official document ——In Power BI Desktop, you'll only have the option to export summarized data as a .csv file.

 

You may firstly refer to @belvoir99 's solution which is very detailed and effective and then publish the report to Power BI service.

Then you could export that data to Excel as an .xlsx or .csv file. The option to export the data requires a Pro or Premium license as well as edit permissions to the dataset and report.

 

For more information, please kindly take a look at these articles:

Export reports to PDF - Power BI | Microsoft Docs

Export entire reports to PowerPoint - Power BI | Microsoft Docs

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

belvoir99
Resolver I
Resolver I

@Babette oh I see 🤔 Sorry I don't have a solution, though here are some approaches I've considered. Can anyone else out there help? Maybe I've missed something obvious? 


You have to keep the fact table in the original format - so you will need to do something completely different. This is because slicers work on a single column and the slicer values are the values from that column.

Out of the box, a matrix puts the values from a single column as column headers and populates from there. So you can't use that. A table allows you to put the columns across the top but there's no way to filter them.

 

I did think about using a solution called 'disconnected tables'. A good example is this: https://p3adaptive.com/2018/05/disconnected-slicers-with-dax-variables-selectedvalues/ 

However, this produces a slicer which will work on the rows, when you want a slicer on the columns. In other words, you want users to filter the columns but keep the original format.

The only other solution I thought is to use Calculation Groups. You end up with something similar to a slicer table which you put in the Columns section of the Matrix visual. It will then appear in the Filters pane (filters on this visual) as 'Name' and your users can then select the columns they want, and then export that. However, the export file unpivots the data back to the format we don't want! 

Sorry I couldn't help any further. 

 

belvoir99
Resolver I
Resolver I

@Babette - best bet is to unpivot the required columns in Power Query. This will switch the data from columns to rows and enable you to place a slicer so that users can filter by rows.

In Power Query, highlight the columns you want to keep as they are, then click on the Transform tab, click on the little arrow next to Unpivot Columns and then click on Unpivot Other Columns. You should now have columns called Attribute & Value, which you can then rename. The Attribute column will hold the column names in the cells.
Then Close & Apply.
Once the data has loaded, use the Attribute column as the slicer column.

Thank you so much @belvoir99 .  I will try this out shortly and send update.

Hi @belvoir99 

In Power Query, I highlighted the columns to keep, then click on the Transform tab, click on the little arrow next to Unpivot Columns and then click on Unpivot Other Columns. It created columns called Attribute & Value. The Attribute column holds the column names in the cells.  Then I Close & Apply.

 

Once the data has loaded, I use the Attribute column as the slicer column.

I use a Matrix - drag Attribute into Columns and Value into Values.  For the Rows, I drag LCode (this is the only field I did not Unpivot).  And i am able to select any Column to display in the Matrix 🙂

The overall results is what I need to see in the power bi report. 

However, the purpose of this report is to enable the client to 'Export data' into CVS for subsquent data analysis.  But CVS output is not in the format they can use 😞 .

Are there other solution I can try please?  

 

fvds
Frequent Visitor

Hi @Babette, and all that are looking for a solution on this topic

I've implemented the solution as described by @belvoir99. This indeed works. However, as @Babette points out, the exported data from the Matrix visual has indeed the unpivotted format.

However, when the report is uploaded in Power BI service, there are more export methods available than in Power BI desktop. In Power BI service the user gets the three options shown below.

Screenshot 2023-01-27 112045.png

 

Exporting the data using the first method outputs the data in the correct format (as shown in the Matrix visual).

 

Good luck!

 

 

Best

 

 

 

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.