Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
swm78
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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

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
Super User
Super User

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

 

Use DAXFormatter.com to check your syntax.

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

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

Top Solution Authors