Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!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.
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
Solved! Go to 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
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
Simplify your code. Don't use SELECTEDVALUE() inside CALCULATE() . Read about EVALUATEANDLOG.
Use DAXFormatter.com to check your syntax.
Check out the May 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.