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

[james_pease]

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!!

[Greg_Deckler]

@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.

[james_pease]

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:

[Greg_Deckler · Accepted solution]

@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.

[james_pease]

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! 

[james_pease]

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

[james_pease]

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!