Forum Discussion

Elscc's avatar
Elscc
Helper I
2 years ago

EXACT function not working with calculated column

I have a table with a text column, and a calculated column which is derived from a measure. The measure is calculating text values from data in a related table. 

 

End result is one table with two text columns with similar text data. 

 

StandardTextColumnCalculatedTextColumn
ValueAValueA
ValueBValueD
ValueCValueB
ValueDValueD

 

I want to highlight where the values do not match.

When comparing the columns, a false outcome is always produced. 

StandardTextColumn = CalculatedTextColumn = False

EXACT(StandardTextColumn,CalculatedTextColumn) = False

 

StandardTextColumnCalculatedTextColumnExpected ResultActual Result
ValueAValueATrueFalse
ValueBValueDFalseFalse
ValueCValueBFalseFalse
ValueDValueDTrueFalse

 

I have checked data types and tried using CONVERT/TRIM to enure both are text data. 

I have tested with other standard text columns within the same table and it works, so it seems to be from the interaction with the calculated text column. 

 

Any help is appreciated. 

Need Help
Tutorial Requests

4 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Can you share the DAX that you have for your measure and calculated column?

    • Elscc's avatar
      Elscc
      Helper I
      2 years ago
      Measure:
      Status =
      VAR _Status =
      IF (
      MAX(DIM_C[ID]) IN SELECTCOLUMNS(
      CALCULATETABLE(RELATEDTABLE('Episodes'),
      ('Episodes'[Date Final Plan Issued]<=TODAY() && NOT(ISBLANK('Episodes'[Date Final Plan Issued]))) &&
      ('Episodes'[Date Episode End]>TODAY() || ISBLANK('Episodes'[Date Episode End])))
      ,"ID",'Episodes'[ID]),"ValueA",
      IF(MAX(DIM_C[ID]) IN SELECTCOLUMNS(
      CALCULATETABLE(RELATEDTABLE('Episodes'),
      ('Episodes'[Date Episode Start]<=TODAY()) &&
      (not(ISBLANK('Episodes'[Date of Request]))) &&
      (ISBLANK('Episodes'[Date of Transfer])) &&
      ('Episodes'[Date Final Plan Issued]>TODAY() || ISBLANK('Episodes'[Date Final Plan Issued])) &&
      ('Episodes'[Date Episode End]>TODAY() || ISBLANK('Episodes'[Date Episode End])))
      ,"ID",'Episodes'ID]),"ValueB",BLANK()
      ))
      RETURN
      IF(ISBLANK(_Status),"ValueC",_Status)
       
      CalculatedColum:
      Status = CALCULATE(IF( ISBLANK([Status]),"ValueC",[Status]),ALLEXCEPT(DIM_C,DIM_C[ID]))
       
      I get the same result using EXACT if I turn the measure into a calucated column like the following:
       
      Status =
      VAR _Status =
      IF (
          DIM_C[ID] IN SELECTCOLUMNS(
              CALCULATETABLE(RELATEDTABLE('Episodes'),
              ('Episodes'[Date Final Plan Issued]<=TODAY() && NOT(ISBLANK('Episodes'[Date Final Plan Issued]))) &&
              ('Episodes'[Date Episode End]>TODAY() || ISBLANK('Episodes'[Date Episode End])),ALLEXCEPT(DIM_CIC,DIM_C[ID]))
              ,"ID",'Episodes'[ID]),"ValueA",
              IF(MAX(DIM_C[ID]) IN SELECTCOLUMNS(
              CALCULATETABLE(RELATEDTABLE('Episodes'),
                  ('Episodes'[Date Episode Start]<=TODAY()) &&
                  (not(ISBLANK('Episodes'[Date of Request]))) &&
                  (ISBLANK('Episodes'[Date of Transfer])) &&
                  ('Episodes'[Date Final Plan Issued]>TODAY() || ISBLANK('Episodes'[Date Final Plan Issued])) &&
                  ('Episodes'[Date Episode End]>TODAY() || ISBLANK('Episodes'[Date Episode End])),ALLEXCEPT(DIM_C,DIM_C[ID]))
                  ,"ID",'Episodes'ID]),"ValueB",BLANK()
              ))
      RETURN
      CALCULATE(IF(ISBLANK(_Status),"ValueC",_Status),ALLEXCEPT(DIM_C,DIM_C[ID]))
       
      I hope this helps. 
  • Shalini_T's avatar
    Shalini_T
    Frequent Visitor
    2 years ago

    Elscc Can you just share the sample formula which you have used to create a measure and calculated column ?
    What is the relationship between text table and related table?

    Regards,

    Shalini

    • Elscc's avatar
      Elscc
      Helper I
      2 years ago

      Hi,

      Please see my other reply for the measure and calculated column formula. My DIM_C and Episode tables have a 1-1 active relationship. 

       

      Many thanks in advance for any help.