Forum Discussion
Select dynamic fields to display in Table visual
Anonymous 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.
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?
Hi Anonymous, 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 Anonymous 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.
Exporting the data using the first method outputs the data in the correct format (as shown in the Matrix visual).
Good luck!
Best