topic DAX Comparing type of Text (Converted Yes and No to 1,0) in DAX Commands and Tips

DAX Comparing type of Text (Converted Yes and No to 1,0)

james_pease — Mon, 14 Nov 2022 17:33:02 GMT

Hello everyone, not sure why I am getting an error that the values are text. The column and measure I am using are both whole numbers. I convereted Yes to 1 and No to 0 in Power Query.

For context, I am created a calculated column to show Pass/Fail, then a second calculated column to show if Fail is anywhere in a critical question for an audit, then the audit is a Fail regardless of other question outcomes. Also, if audit score is below 80%, result in an audit fail.

Here is the expression for the calculated column with an error:

Pass/Fail Per Question =

CALCULATE (

IF (

'Audit_Answers'[Audit Score measure] = 0

&& MAX ( 'Audit Questions'[Critical] ) = 1,

"Fail",

IF (

'Audit_Answers'[Audit Score measure] > .8

&& MIN ( 'Audit_Answers'[Critical] ) = 0,

"Pass",

"Fail"

)

FILTER (

'Audit_Details',

[Response_ID] = EARLIER ( 'Audit_Answers'[Response_ID] )

)

Here is a screenshot of the column and to show the type is not text:

Im sure the solution is super simple and I am overlooking something. Thank you in advance!!

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

Greg_Deckler — Mon, 14 Nov 2022 18:21:41 GMT

james_pease Generally using a measure in a calculated column is not a thing. Calculated columns are not dynamic like measures and are calculated at the time of data load.

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

james_pease — Mon, 14 Nov 2022 18:25:36 GMT

Got it! So essentially spell out the formula of the measure in the calculated column.

Edit* just tried it and I am still yielding the same error:

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

Greg_Deckler — Mon, 14 Nov 2022 18:39:39 GMT

james_pease Try this:

Pass/Fail Per Question = VAR __ResponseID = [Response_ID] VAR __Responses = FILTER(ALL('Table'),[Response_ID] = __ResponseID) VAR __AuditScore = SUMX(__Responses, [Audit_Score]) VAR __Min = MINX(__Responses, [Critical]) VAR __Max = MAXX(__Responses, [Critical]) VAR __Result = SWITCH(TRUE(), __AuditScore = 0 && __Max = 1, "Fail", __AuditScore > .8 && __Min = 0, "Pass", "Fail" ) RETURN __Result

At least you will be able to troubleshoot this.

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

james_pease — Mon, 14 Nov 2022 18:47:24 GMT

Thank you sir, I will need to figure out how to wrap it so I dont get a circular dependacy for the next calculated column. Thank you for your help!

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

james_pease — Mon, 14 Nov 2022 19:11:12 GMT

Sorry to bother you again, I am getting a circular dependency with itself. I think it had to do with the filter(all.

Fixed with the following:

Pass/Fail Per Question =

VAR __ResponseID = [Response_ID]

VAR __Responses = ALLEXCEPT('Audit_Answers',Audit_Answers[Response_ID])

VAR __AuditScore = SUM(Audit_Answers[Audit Score])

VAR __Min = MINX(__Responses, [Critical])

VAR __Max = MAXX(__Responses, [Critical])

VAR __Result =

SWITCH(TRUE(),

__AuditScore = 0 && __Max = 1, "Fail",

__AuditScore > .8 && __Min = 0, "Pass",

"Fail"

)

RETURN __Result

Re: DAX Comparing type of Text (Converted Yes and No to 1,0)

james_pease — Mon, 14 Nov 2022 19:25:53 GMT

Final reply, got the two columns to work:

Pass/Fail Per Question =

VAR __ResponseID = [Response_ID]

VAR __Responses = ALLEXCEPT('Audit_Answers',Audit_Answers[Response_ID])

VAR __AuditScore = SUM(Audit_Answers[Audit Score])

VAR __Min = MINX(__Responses, [Critical])

VAR __Max = MAXX(__Responses, [Critical])

VAR __Result =

SWITCH(TRUE(),

__AuditScore = 0 && __Max = 1, "0",

__AuditScore > .8 && __Min = 0, "1",

"0"

)

RETURN __Result

Pass/Fail Audit =
CALCULATE (
IF ( MAX ( Audit_Answers[Pass/Fail Per Question] ) = 1, "Pass", "Fail" ),
FILTER ( Audit_Answers, [Response_ID] = EARLIER ( Audit_Answers[Response_ID] ) )
)

Thank you again!! You helped me a ton!