Filter parameter table on other slicer

[Theo1403]

Hi,

I have a report with a table in it. One of the columns is a field parameter. The field parameter contains 2 columns (column 1 en column 2) from the dim_supplier table. The report also contains a slicer on another dimension table. If one option in the slicer is selected, column 1 should show in the table, if multiple options are selected, column 2 should show in the table. I am sure this can be achieved by a visual level filter on the table, but I have not been able to get the solution right.  Anyone already solved this problem?

Best regards,

Theo

[v-hashadapu]

Hi @Theo1403 , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @Theo1403 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Praful_Potphode]

Hi @Theo1403 ,

Please try sample pbix.

if it doesnt work,please provide more information on output.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Theo1403]

Hi @Praful_Potphode ,

Thanks for your reply. However in your solution, you don't use the field parameter, Filtering the field parameter table seems like the best solution to me. Your solution works in the smal sample pbix, but my table contains a lot of supplier rows, so the solution is not really scalable in terms of performance. Swapping dimension columns by using a slicer on the field parameter table works extremely fast, I am looking for a solution to slice the parameter table without having to use that slicer.

Please let me know when you have an idea how to fix this!

Regards,

Theo

[Praful_Potphode]

Hi @Theo1403 

Thanks for your response 

Can you provide any sample data or any snapshot of input/output ?

Thanks and regards,

Praful

[Theo1403]

Hi @Praful_Potphode ,

My model has the same characteristics as your sample pbix, it is just a lot bigger (dimension table > 6,000 rows). If you place the parameter in the table visual, you get both columns (Column1 and Column2). The table should show Column1 (including Column 1 header) when 1 Region is selected and Column 2 when more than 1 Region is selected. The same behavior you would achieve when you add a slicer on the Parameter Order column (that column is currently hidden). I just do not want to add the parameter slicer to the report, but want to slice the parameter table based on the number of selected regions.

Hope you can help

Regards,

Theo

[v-hashadapu · Accepted solution]

Hi @Theo1403 , Thank you for reaching out to the Microsoft Community Forum.

A field parameter only switches columns when the parameter table is actually part of the visual’s evaluation context. Once you remove the parameter slicer, the parameter table stops being filtered, so Power BI has no way to know which parameter row should be active. That’s why every automatic attempt ends up with a blank table.

To make it work, the parameter table just needs to participate in the visual. The simplest way is to add the parameter’s Order column to the table visual and hide it, so users never see it. With that column present, you can use a small measure to decide whether row 0 or row 1 should be active based on how many Regions are selected and then apply that measure as a visual level filter. The field parameter will switch instantly and you keep its native performance.

If you’d rather not include even a hidden parameter column in the visual, then the parameter can’t be sliced indirectly. In that case the practical alternative is a measure that checks the Region selection count and returns Column1 or Column2 text for each supplier row. It doesn’t use the field parameter, but it avoids the context problem entirely and performs well even on large supplier tables.

[Hans-Georg_Puls]

Hi @Theo1403 , if your only requirement is "If one option in the slicer is selected, column 1 should show in the table, if multiple options are selected, column 2 should show in the table." and you are fine with a fixed column name, the following measure should work as your dynamic column:

Dynamic Column =

    VAR _selected = SELECTEDVALUE(dim_other[Other], BLANK())

    VAR _selectedCol1 = SELECTEDVALUE(dim_supplier[Column1], BLANK())

    VAR _selectedCol2 = SELECTEDVALUE(dim_supplier[Column2], BLANK())

RETURN

    IF(ISBLANK(_selected), _selectedCol2, _selectedCol1)

Hope that helps!