Forum Discussion
Helper IUsing list of values in IN function
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?
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_QIf 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
2 Replies
- lbendlin
Super User
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.
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_QIf 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
