Create filter to select category in column but not filter all data

[sparker95]

Below is a redacted version of table I have in PowerBI, I need to create a graph that shows the £/ft2 by Elemental Cost Plan.
However I want to also have a slicer that allows me to pick my Cladding values e.g 'Brick' and then only show that one value for the 'External Works' element of the 'Elemental Cost Plan'. I can create the slicer but I'm sturggling with the DAX measure. 

Project	Elemental Cost Plan	Sub Element	Cladding	£/ft2	Total £
Baseline	Substructure	Basement		11	557,160
Baseline	Substructure	Unallocated		10	506,509
Baseline	Frame			14	698,070
Baseline	Upper Floors			16	798,867
Baseline	Roof			9	459,154
Baseline	Stairs & Ramps			2	93,888
Baseline	External Walls		Brick	44	2,259,539
Baseline	External Walls		Brickslip	40	2,057,627
Baseline	External Walls		Precast	26	1,337,458
Baseline	External Walls		Unitised	60	3,086,441

[Anonymous · Accepted solution]

Hi @HotChilli ,thanks for the quick reply, I'll add more.

Hi @sparker95 ,

The Table data is shown below:

Please follow these steps:

1. Use the following DAX expression to create a table for slicer

Table 2 = VALUES('Table'[Cladding])

2.Use the following DAX expression to create a measure

Measure = 
VAR _Slicer = VALUES('Table 2'[Cladding])
VAR _Elemental = SELECTEDVALUE('Table'[Elemental Cost Plan])
RETURN 
IF(_Elemental = "External Walls",SUMX(FILTER('Table','Table'[Cladding] IN _Slicer),[£/ft2 ]),SUM('Table'[£/ft2 ]))

3.Final output

Best Regards,
Wenbin Zhou

[HotChilli]

I think I see what you want. It would be a better idea to show the desired result, starting from the sample data. So please do that next time.

I think you can do this already by multi-selecting items in your slicer (blank will return the non-"external walls" and you can pick any one of the other values using ctrl click)

--

If I was doing it, I might add a column to give a value to all those blank/empty values in Cladding. So, in Power Query, something like:

if [Cladding] is empty/null then "Other" else [Cladding]

Create a slicer from this column (putting it in a dimension table and relating 1:n would be better) and then you can multi-select "Other" and any Cladding value.

Let me know how you get on.

[sparker95]

Sorry typo I meant 'External Walls' , if you see the below screenshot. It would look like this setup, but rather than the 'Cladding' just filtering to External Walls, it would keep all other elements and then filter down the £/ft2 of 'External Walls' to only the selected value. E.g you select 'Brick' and the cost of External Walls would be £44/ft2. But all other elements would still show. Thanks

[HotChilli]

Please show the desired result.  It's not clear what you are trying to do.

"only show that one value for the 'External Works' element of the 'Elemental Cost Plan" - what is this? 'External Works' element isn't anywhere in the table.