Forum Discussion
Dynamic report and dynamic column based on a Slicer
I built a Power BI report where the main logic depends on a Conditional Column called “Category,” defined as:
- If Company Type equals A, then A, else Others.
All chart legends and filters are based on this Category column.
However, I also have Company B, C, D, up to L. To switch the focus to a different Company Type, I currently need to manually edit the Conditional Column in Power Query. Because the Category column always displays one specific category and “Others.” I also would like to display both the selected category and “Others” in the charts dynamically.
Is there a way to achieve this using a slicer instead of modifying the Conditional Column each time? If so, how?
3 Replies
- lbendlin
Super User
Is the result sensitive, ie should the report users not be able to identify which companies are in the "Other" bucket?
It is not really sensitive, just one company compare with the rest company? I have few company need this report. Each time the report only can show one company vs rest company.
Hi Heymm ,
Thanks lbendlin for the quick reply. I have some other thoughts to add:
(1) This is my test data.
(2) We can create a slicer table and a Category measure. Note that there is no model relationship between the slicer table and the original table.
Slicer Table = VALUES('Table'[Company Type])Category = IF(SELECTEDVALUE('Slicer Table'[Company Type])=MAX('Table'[Company Type]),MAX('Table'[Company Type]),"Others")Since Category is a measure, it cannot be placed on a slicer visual object, but you can place the measure on a visual object filter.
(3) Calculated columns can't dynamically get the value selected by the slicer, so we need to create three columns.
A = IF([Company Type]="A","A","Others")B = IF([Company Type]="B","B","Others")C = IF([Company Type]="C","C","Others")Create field parameters.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
