Forum Discussion
Filter blank rows from table with field parameter
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.
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.
2 Replies
jgeddesSuper User
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.Thank you so much! This works and was easy to follow.
