Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
james_pease
Helper III
Helper III

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

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:
james_pease_0-1668446377045.pngjames_pease_2-1668447173906.png

 

james_pease_1-1668446832249.png
 

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

 



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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

james_pease_0-1668450329405.png

 



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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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! 

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors