DAX 'IN' operator not working as expected (at all actually)

[Nickodemus]

Hi All,

I'm getting a syntax error when using the 'IN' operator - it's not presenting as an error in the DAX editor bar (i.e. the measure doesn't present as an error), but only when the measure is used in a visual - e.g. the measure appears to be fine, and showing no errors, but then i use the measure in a tile, and it errors stating there is a syntax error.

I've also tried writing the measure in DAX in an SSAS tabular model, and do get the same syntax error - if someone could assist I'd appreciate it.

Current Sales Count := 
CALCULATE(
DISTINCTCOUNT('FactSales'[Sales ID]),
	FILTER('dimSalesAttributes',[Current Status] IN { "Held","Reserved" })
)

I've used 'IN' before in exactly the same way, and it's worked fine, but this is odd. I've also tried literally copying and pasting the 'IN' example from the DAX documentation and simply changed the field references, and still get the syntax error. 

I realise the Filter function isn't necessary, but it's in this one as it's the last version i tried.

Many thanks,

Nick

[Anonymous]

@Nickodemus try this

Current Sales Count :=
CALCULATE(
DISTINCTCOUNT('FactSales'[Sales ID]),
FILTER('dimSalesAttributes','dimSalesAttributes'[Current Status] IN { "Held","Reserved" })
)

[Nickodemus]

Thanks @Anonymous, but i gives the same semantic error:

"Semantic Error: The syntax for 'IN' is incorrect."

I've had to resort to old school:

'dimSalesAttributes'[Current Status] = "Held" || 'dimSalesAttributes'[Current Status] = "Reserved", etc, etc

...which works fine, but is quite frustrating...

Any other ideas?

[Anonymous]

@Nickodemus can you share your power bi file so i can see whats happening, its difficult to debug the issue without knowing all the facets. I tested the above code and it worked for me.

[Nickodemus]

Can't I'm afraid @Anonymous, due to sensitive data.... it is currently very basic, however.

Single fact table containing sales facts, and a sales ID (which I'm trying to do distinct count on). Connected to a dimSalesAttribute table (essentially a junk dim) containing statuses, flags, and other dimensions relating to the facts. The relationship is one directional (1:*  - dim:fact).

I am trying to do distinct count on the sales ID column of the fact table based on several values from the [current status] dimension from the attibutes (junk dim) table. Figured it'd be easy enough to simply use the DAX expression as above... distinct count where attribute is in 'Held' or 'Reserved'......

[v-cherch-msft]

Hi @Nickodemus

You may try to update the version of desktop.

Regards,

Cherie

[Nickodemus]

Thanks for the suggestion @v-cherch-msft, but I am running the most recent versions of SSMS, Visual Studio, and Power BI.

[Nickodemus]

Can't I'm afraid @Anonymous, due to sensitive data.... it is currently very basic, however.

Single fact table containing sales facts, and a sales ID (which I'm trying to do distinct count on). Connected to a dimSalesAttribute table (essentially a junk dim) containing statuses, flags, and other dimensions relating to the facts. The relationship is one directional (1:*  - dim:fact).

I am trying to do distinct count on the sales ID column of the fact table based on several values from the [current status] dimension from the attibutes (junk dim) table. Figured it'd be easy enough to simply use the DAX expression as above... distinct count where attribute is in 'Held' or 'Reserved'......

[Anonymous]

@Nickodemus could you try creating a workbook with dummy data and try reproducing the problem?

[Nickodemus]

Thanks again @Anonymous - I've done as you suggest, and recreated extremely slimmed down versions of the same tables in CSVs, and added those to new PBI model..... And the measure works fine!! 

I then went back to my SSAS tabular model... cut it right back to only the fields in question, and I'm still getting the same issue... syntax error with 'IN'! I copied and pasted it!

I'm totally at a loss as to why it's not working. The only thing i can think of is that there is something in my SSAS model that isn't configured to use all the DAX functions??? is that possible?

Is it something to do with SSAS...? I have just tested another way, but importing the SQL tables directly into PBI, and creating the measure, and it works fine.... So there is something to do with creating the model in SSAS that both Visual Studio and PBI isn't liking when i attempt to create the SSAS model.

[Anonymous]

Hi, I also have the same issue... - Have you found a solution for this?

[Anonymous]

@Nickodemus unfortunately I am not sure about this one. Could you try rebuilding the model?

Also the formula for IN are you adding the calc to the model or to the power bi file? try oppositte of what you are doing?