SelectedValues will not filter on multiple values

[rwyoung01]

I am trying to filter a table based on  certain values in a column and am getting an error message that the expression refers to multiple columns which cannot be converted to a scalar value

AccountTypeWPrisoner = if (SELECTEDVALUE('Main Account'[AccountType]) = "Main Account Types(with Prisoners)", FILTER(Accounts,Accounts[AccountType] in {"Business","Chaplain","Church","Directgiv","Ministry","Minor","Prisoner","Regular","Orgcontact"}))

[ElenaN]

Hello,

What data types are 'Main Account'[Account Type] and 'Accounts'[Account Type] ?

Regards,

ElenaN

[rwyoung01]

Text

[v-cherch-msft]

Hi @rwyoung01 

You may try below measure and drag it into table visual or use it as visual level filter.If it is not your case,please show us some screenshot of the table and error.

AccountTypeWPrisoner =
IF (
    SELECTEDVALUE ( 'Main Account'[AccountType] ) = "Main Account Types(with Prisoners)"
        && MAX ( Accounts[AccountType] )
            IN {
            "Business",
            "Chaplain",
            "Church",
            "Directgiv",
            "Ministry",
            "Minor",
            "Prisoner",
            "Regular",
            "Orgcontact"
        },
    1
)

Regards,

Cherie 

[rwyoung01]

ok, that eliminated the error but no charts on that page get filtered.  Here is what I am trying to do.  I have an Account  table with the AccountType as one of the columns.  I have added a ClicletSlicer that has two options.  When I select "Main Account Types(with Prisoners), I want it to filter the Account Table.  It would be the same as adding the AccountType as a Page Level or Report Level filter and making the user select which types of AccountType he wants to filter on.  But I want it to be done using the ClicletSlicer to do the filtering of all charts on that page that use the Accounts table.   I added the above code as a Measure on the Accounts table.  

[v-cherch-msft]

Hi @rwyoung01 

You may try to add the measure to visual level filters for all the charts which need to be filtered like below:

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

Regards,

Cherie

[rwyoung01]

My user would get confused.  I want to use a Chiclet slicer 

---

@v-cherch-msft wrote:

Hi @rwyoung01 

You may try to add the measure to visual level filters for all the charts which need to be filtered like below:

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

Regards,

Cherie

---

---

@v-cherch-msft wrote:

Hi @rwyoung01 

You may try to add the measure to visual level filters for all the charts which need to be filtered like below:

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

Regards,

Cherie

---

[v-cherch-msft]

Hi @rwyoung01 

The visual level filter can be used to filter the visual.Please refer to the attached sample file.

Regards,

Cherie

[rwyoung01]

This is closer but I have to apply it to all the objects on the page and there are several pages.  Is there no way to make it work like the visual filter for Account Type that when you select a type it affects all objects that reference that table.  

[v-cherch-msft]

Hi @rwyoung01 

You may create a column in Accounts table and link the two tables in relationship view.For example:

Key = 
IF (
    Accounts[AccountType] IN { "Business", "Chaplain" },
    "A",
    IF ( Accounts[AccountType] IN { "Other" }, "B" )
)

Regards,

Cherie 

[rwyoung01]

If I use a Chiclet Slicer that uses a measure to select the rows of data from the Account table that I want, I have to add that measure to every Object on the page, unlike the Visual filters for AccountType that make me select each AccountType manually but then filters all objects on the page or report that uses the Account table.  I have included a screen shot.  What I am trying to accomplish is similar to a feature in QlickView where a button can have a SQL like statement like 'Select in Field AccountType (Business,Chaplain,Church)' . Once this button is clicked on alll objects that use the Account table are then filtered.