Dynamic report and dynamic column based on a Slicer

[Heymm]

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?

[Anonymous]

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. 

[Heymm]

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. 

[lbendlin]

Is the result sensitive, ie should the report users not be able to identify which companies are in the "Other"  bucket?