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

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

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
v-stephen-msft
Community Support
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

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
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

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors