Filtering Multiple Criteria Across Rows and Columns

[fitzgeec]

Hello all!

I have a scenario where we want to compare systems and capabilities and need some help on figuring out how to complete the filtering needed for this task.  We have a spreadsheet that will have around 24 systems and over 500 capabilities and we need to be able to see how filtering out systems impacts the capabilities.  I have put together a small scale example to demonstrate what I am trying to do.  What I have not been able to do is figure out how to get the filtering required to work in Power BI Desktop.  Any suggestions would be greatly appreciated!

In the examples below I have a matrix for systems A, B, C, and D and capabilities 1, 2, 3, 4, and 5.  Within the matrix a 1 represents that the system has that capability and a null value in the cell represents that the system does not have that capability.  As shown below, if system C is filtered out, the capabilities for system C would be removed which is easily accomplished with a slicer.  The additional functionality that I am trying to do and cannot figure out is that other sysems that have the same capabilities as system C should have those capabilities removed (or filtered out...below I set them to zero to show a change to those values).  But I do not want to change capabilities for the other systems if they do not correspond to the capabilties that the filtered system has.

Thanks in advance for any suggestions!

	System
Capability	A	B	C	D	Total:
1	1	1			2
2		1	1	1	3
3			1	1	2
4			1		1
5	1			1	2
Total:	2	2	3	3	10
If System C is filtered out, any other systems with the same capabilities should have those removed or filtered out.
Example:
	System
Capability	A	B	C	D	Total:
1	1	1			2
2		0	0	0	0
3			0	0	0
4			0		0
5	1			1	2
Total:	2	1	0	1	4
If System A is filtered out, any other systems with the same capabilities shuld have those removed or filtered out.
Example:
	System
Capability	A	B	C	D	Total:
1	0	0			0
2		1	1	1	3
3			1	1	2
4			1		1
5	0			0	0
Total:	0	1	3	2	6

[Anonymous]

Hi @fitzgeec ,

Based on your description, the preferred option is that you need to create a list as a filter based on the system field.
Then try the following formula to see if it meets your requirements.

slicer = VALUES('Table'[Stytem])

M1 = 
VAR cur =
    SELECTEDVALUE ( slicer[Stytem] )
RETURN
    IF (
        CUR = MAX ( 'Table'[Stytem] )
            && MAX ( 'Table'[Value] ) = 1,
        0,
        MAX ( 'Table'[Value] )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[fitzgeec]

Thank you so much for your reply!  I have a few questions to fully understand what you did.  What does your table look like?  It seems like my table may be a little different than yours.  I have a spreadsheet that has one column for the System and one column for the Capabilities (see image below). 

The code you put for slicer = VALUES('Table'[Stytem]), where did you apply that?  Did you create that as a measure under the table?  If so, then where did you use that measure?

Also, the code for the M1, is that a measure and where did you use that measure?

I apologize for the questions but I am new to Power BI and trying to get my head around how to use all of this.  Thanks again for your help!