cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

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

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Regular Visitor

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

Super User

Use DAXFormatter.com to check your syntax.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors