Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |