CODEWITHSUNDEEP
t-sqlpowerbidax
Ethan Pelton
Ethan Pelton

Reputation: 1796

Power BI Field Parameter Filtering

I have a table with the bit columns shown in the socioeconomic slicer below - Minority Business, Small Business, etc. I've used the Field Parameter feature to add them to accomplish the slicer below.

enter image description here

Vet Owned Small Business is selected. The current behavior is that the table on the left will show each column selected on the right. Instead, I want to only show rows that are True for Vet Owned Small Business. I do not want Vet Owned Small Business to be displayed in the table on the left - only table should be filtered for True on the selected slicer items.

Field Parameters may not be the way to go. I'm happy to try a different method to accomplish this.

Upvotes: 2

Views: 2755

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

Let, you have this 2 tables - "column_slicer" and "your_table_name"

enter image description here

You can create this below measure (hope you do not have too many columns in the slicer). You can add more columns to the measure with || condition.

show_hide = 
VAR _selected = SELECTEDVALUE ( column_slicer[value])

RETURN
IF (
    (_selected = "column_1" && SELECTEDVALUE(your_table_name[column_1]))
        || (_selected = "column_2" && SELECTEDVALUE(your_table_name[column_2]))
        || ISBLANK(_selected),
    1,
    0
)

Now add the newly created Measure in the table filter property as shown below-

enter image description here

You will have a final output as below-

enter image description here

Upvotes: 4

Luke_0
Luke_0

Reputation: 855

If I am understanding you right, you have a true/false column on the fact table for each vendor attribute. You want to filter your vendor list based on these columns.

The best way to do this is to convert your data from structured to unstructured. Structured data is what you have now, a column for each attribute. Unstructured data would simply have three columns:

Vendor Number | Attribute       | Value
--------------+-----------------+-------
1234          | Vet Owned       | TRUE
1234          | Minority Owned  | FALSE
5678          | Vet Owned       | TRUE
5678          | Minority Owned  | TRUE

You can accomplish this in Power Query (Transform Data) by Un-Pivoting the attribute columns (Transform > Unpivot Columns > Unpivot Only Selected Columns). This will create multiple records for each vendor, one for each attribute. Filter this table to where the value is TRUE. Now, load this to the model as a new table, create a bi-directional relationship between it and the current vendor table, and use the "Attribute" column in the slicer.

This will filter your main vendor table where the selected attribute(s) are TRUE (default behavior for multiple selections will be OR).

Upvotes: 1

Related Questions