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
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] =
) > 0,
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
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.