Create column to check if the value is part of a list

[swm78]

Hi BI chaps, First question on this site, so I hope this finds you well.

I need to check if a value is present in a list so I used the "counta" formula to see that. (Eventually i need 1 to return the error and 0 means the value is in the list)

I need to check this on multiple columns and add the result to see if any of the columns has an error.

Here is the formulat I tried:

IF(
CALCULATE(
COUNTA(AML_Codes[Code])
,FILTER(AML_Codes,SELECTEDVALUE(Mfgi_Reg_Stats[BLOCK_CODE])=SELECTEDVALUE(AML_Codes[Code]))
,FILTER(AML_Codes,SELECTEDVALUE(AML_Codes[Field])="Block_Code"))=0 THEN 1 ELSE 0)

It tells me that "Token Comma expected"

So I tried this:

IF(
CALCULATE(
COUNTA(AML_Codes[Code])
,FILTER(AML_Codes,SELECTEDVALUE(Mfgi_Reg_Stats[BLOCK_CODE])=SELECTEDVALUE(AML_Codes[Code]))
,FILTER(AML_Codes,SELECTEDVALUE(AML_Codes[Field])="Block_Code"))=0 ,1, 0)

It then says "No syntax error have been detected" but then when I hit enter, I get "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly"

Btw the last formula here works when creating a measure, but i need to create a column to check the calculation + use them for a sum of all columns

I also tried the CONTAINS function with no success, but the counta fits better as I will replicate this on multiple columns to then add up the errors

Any idea how to do this? Thanks a lot

[v-stephen-msft · Accepted solution]

Hi @swm78 ,

About matching values from another table by calculated columns, you can refer to

DAX - “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 - P3 Adaptive

Main formula for the calcualted column:

[Is this company a metals company] =
IF(
      SUMX(MatchList,
           FIND(
                UPPER(MatchList[Keyword]),
                UPPER(Companies[Company])
                ,,0
               )
          ) > 0,
      "0",
      "1"
     )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[swm78]

I had to change my data because i was not able to filter the above but it then resolved perfectly my issue.

Thanks a lot

[lbendlin]

Simplify your code. Don't use SELECTEDVALUE() inside CALCULATE() . Read about EVALUATEANDLOG.

Use DAXFormatter.com to check your syntax.