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 ...
Need Help
Tutorial Requests
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.