Filter blank rows from table with field parameter

[iamiram]

I have a table with lots of blank values. I'm using a field parameter to let the user chose which columns to show in a table visual. I'd like the visual to filter out rows with a blank value for the selected column(s).

For example:

NAME	COLOR	SHAPE	SIZE
apple	red	round	medium
banana	yellow
cherry			small
durian		spiky
elderberry
fig	purple		small

If the user slects COLOR and SHAPE in the field parameter slicer, the table visual should be:

NAME	COLOR	SHAPE
apple	red	round
banana	yellow
durian		spiky
fig	purple

I'm still a relatviely new Power BI user, so any ideas would be appreciated.

[jgeddes · Accepted solution]

There are likely a few different ways to do this but I would generally approach it like this...

I would 'unpivot' your table in the Power Query editor.

Select 'NAME' column and select Transform->Unpivot Columns->Unpivot Other Columns

You should now have...

Close and Apply to return to the BI screen.

You can create a measure to return the minimum non-blank value.

Value (not Blank) = 
//create a virtual table that has no blank 'values'
var _vtable =
FILTER(fruitTable, fruitTable[Value] <> "")
return
//return the minimum value from the virtual table
MINX(_vtable, fruitTable[Value])

You can now build the matrix visual with Rows = [NAME], Columns = [Attribute] and Values = [Value (not blank)]

to get...

You can use the 'Attribute' column as slicer for the user to select which attributes they want displayed.

[iamiram]

Thank you so much! This works and was easy to follow.