Using list of values in IN function

[RyanHare92]

Hi,

I have the following measure:

Status = 
VAR _Series_Month =
    LEFT(MAX(all_series[series]), SEARCH(" ", MAX(all_series[series])) - 1)

VAR _Key_Markets =
    SWITCH(
        _Series_Month,
        "March", 
            {"Country1"},
        "June", 
            {"Country2", "Country3", "Country4"},
        "November", 
            {"Country5", "Country6"},
        BLANK()
    )

VAR _Key_Market_Q =
    CALCULATE(
        MAX(key_Customer_Entries[Q%]),
        key_Customer_Entries[country] IN _Key_Markets,
        key_Customer_Entries[ses_name] = MAX(all_series[series])
    )

RETURN 
    _Key_Market_Q

However, I keep getting the following error:

The function expects a table expression for argument '', but a string or numeric expression was used.

How do I pass the values from _Key_Markets into _Key_Market_Q correctly?

[Anonymous · Accepted solution]

Hi @RyanHare92 ,

You can follow the steps to get it:

1. Create a dimension table as below

2. Update the formula of your measure[Status] as below 

Status =
VAR _Series_Month =
    LEFT (
        MAX ( all_series[series] ),
        SEARCH ( " ", MAX ( all_series[series] ) ) - 1
    )
VAR _Key_Markets =
    CALCULATETABLE (
        VALUES ( 'Table'[Countries] ),
        FILTER ( 'Table', 'Table'[Month] = _Series_Month )
    )
VAR _Key_Market_Q =
    CALCULATE (
        MAX ( key_Customer_Entries[Q%] ),
        key_Customer_Entries[country] IN _Key_Markets,
        key_Customer_Entries[ses_name] = MAX ( all_series[series] )
    )
RETURN
    _Key_Market_Q

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with special examples and screenshots. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[lbendlin]

This is a dilemma.  The SWITCH statement cannot return objects, only simple data types. 

You need to refactor the query to pull the switch into the final step, or use SEARCH  instead with a string concatenation of your match terms.