Column Displayed in a table Based on Slicer Selection

[Anonymous]

Hi,

This may be a really silly question, but I am trying to use a slicer value to filter a table.

In the following example 

OptionSelected = SELECTEDVALUE(YesNo[Answer]) which can be yes or no

Table =

Var Option = 'BI Measure'[OptionSelected]

RETURN

IF (Option = "Yes",

FILTER(Merchants_Parent,Merchants_Parent[MerchantStatus] <> ""),

FILTER(Merchants_Parent,Merchants_Parent[MerchantStatus] <>"Closed")

)

What I want is to create a table with or withioiut closed Merchants in, I keep getting "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Any help would be appreciated

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

In Power BI, it doesn't support to filter a table based on a filter.

The physical table is static.

You could work with a workaround as below.

Create measure, add columns from the Merchants_Parent table in a "Table" visual, then add this measure in the visual level filter f the table visual.

Table = 
Var Option = [OptionSelected]
RETURN
IF (Option = "Yes",
CALCULATE(COUNTROWS(Merchants_Parent),FILTER(Merchants_Parent,Merchants_Parent[MerchantStatus] <> "")),
CALCULATE(COUNTROWS(Merchants_Parent),FILTER(Merchants_Parent,Merchants_Parent[MerchantStatus] <>"Closed"))
)

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you this is great 🙂