topic Re: Using list of values in IN function in DAX Commands and Tips

Using list of values in IN function

RyanHare92 — Wed, 24 Jan 2024 16:58:28 GMT

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?

Re: Using list of values in IN function

lbendlin — Wed, 24 Jan 2024 20:00:50 GMT

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.

Re: Using list of values in IN function

Anonymous — Thu, 25 Jan 2024 09:56:20 GMT

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