Forum Discussion
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 ...
Can you share the DAX that you have for your measure and calculated column?
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)
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.