topic Need help with Dynamic Multi-Column Filtering in Developer

Need help with Dynamic Multi-Column Filtering

Anonymous — Thu, 11 Nov 2021 19:46:01 GMT

Basically, I have a table in the database that looks like this with a bunch of report names as rows and a few thousand 1/0 indicator columns:

Name	Measure1	Measure2	Measure3	Measure4	…	Measure3000
Report1	1	1	0	0		1
Report2	0	1	1	0		0
Report3	0	0	0	0		1
Report4	1	1	1	1		0
Report5	0	1	0	1		0
…

I want to create some sort of visual that will allow the user to select any number of measures (EG measure1 and measure3) and the table will filter down to show only report names where the measures selected by the user = 1

Example1: if the user selects measure1, reports 1 and 4 should be shown.

Example2: If the user selects measure2 and measure3, reports 2 and 4 should be shown

Example3: If the user selects measure1, measure2 and measure3000, no reports should be shown

I have managed to get this functionality by putting many slicers on the canvas, however putting 100+ slicers on the same canvas is not plausible.

Is this something that can be done?

Re: Need help with Dynamic Multi-Column Filtering

AlexisOlson — Thu, 11 Nov 2021 20:25:55 GMT

Unpivot the measure columns in the query editor and this is much easier.

Re: Need help with Dynamic Multi-Column Filtering

Anonymous — Fri, 19 Nov 2021 09:22:53 GMT

Hi @Anonymous ，

Are the fields circled in red in the screenshot below measures or the fields that actually exists in the table? If they are measures, you can refer to the following blog or video to dynamically display the measure values based on the slicer option.

Power BI - Dynamically Add or Remove Measures from Visual

Multi Measure Dynamic Visuals - Data Viz Technique In Power BI

Switching Measures and Titles Dynamically in Power BI

If the above links can't help get the expected result, please provide some sample data(exclude sensitive data) and the formula of measures. It is better if you can provide a simplified pbix file. Thank you.

Best Regards

Re: Need help with Dynamic Multi-Column Filtering

Anonymous — Thu, 09 Dec 2021 02:11:36 GMT

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

Re: Need help with Dynamic Multi-Column Filtering

Anonymous — Thu, 09 Dec 2021 14:11:38 GMT

Solution has been solved internally by unpivoting the table (as suggested by AlexisOlosen) and calculating the following measure:

ShowRow =

VAR selColCount = COUNTROWS(ALLSELECTED(ReportMatrix[Attribute]))

VAR sumSelCols = COUNTROWS(filter(ALLSELECTED(ReportMatrix[Attribute]),[Val]))

RETURN

if(sumSelCols = selColCount, 1, 0)

This basically sums up the number of columns selected by the user and compares that to the sum of the values of each of those columns (either 0 or 1). If the sum of the column values = the number of columns selected then Showrow = 1, otherwise 0. Then we simply filter on Showrow