CODEWITHSUNDEEP
daxssas-tabular
DevMJ
DevMJ

Reputation: 351

DAX in calculated column

I have two tables as below.

Table1
CaseId
66787

Table2
PrimaryKey  CaseId  SeqNo   Status   Primary    Code    CodeCareNo
85248       66787   6       Active     N         876    8775568
70728       66787   1       Inactive   N         876    3661794
79008       66787   5       Active     Y         876    3766066
86868       66787   7       Active     Y         876    3287735

Table 1 has one to many relationships with Table2 and the relating column in CaseId. I have a requirement to create a calculated column in Table1 in my model project. The calculated column should display a text like (Code) CodeCareNo (eg: (876) 3766066) for each CaseId in Table1 with the values from Code and CodeCareNo columns of Table2 which has Primary = “Y” and Status = “Active” with Seq No as the minimum value among the primary active code numbers for the CaseId. Also if the Code or CodeCareNo is null the calculated column should show blank value. I am able to get the desired result with below query but I feel it bit messy. Can someone help me in simplifying the same? 

=IF("(" & LOOKUPVALUE(Table2[Code], Table2[CaseId], Table1[CaseId], 
Table2[Primary], "Y", Table2[Status], "Active", Table2[SeqNo],     
MINX(FILTER(Table2, ( Table2[Primary] =  "Y" && Table2[Status] = "Active" && 
Table2[CaseId] = Table1[CaseId])), Table2[SeqNo])) & ") " & 
LOOKUPVALUE(Table2[CodeCareNo], Table2[CaseId], Table1[CaseId], 
Table2[Primary], "Y", Table2[Status], "Active", Table2[SeqNo], 
MINX(FILTER(Table2, ( Table2[Primary] =  "Y" && Table2[Status] = "Active" && 
Table2[CaseId] = Table1[CaseId])), Table2[SeqNo])) = "() ", BLANK(), "(" & 
LOOKUPVALUE(Table2[Code], Table2[CaseId], Table1[CaseId], Table2[Primary], 
"Y", Table2[Status], "Active", Table2[SeqNo], MINX(FILTER(Table2, ( 
Table2[Primary] =  "Y" && Table2[Status] = "Active" && Table2[CaseId] = 
Table1[CaseId])), Table2[SeqNo])) & ") " & LOOKUPVALUE(Table2[CodeCareNo], 
Table2[CaseId], Table1[CaseId], Table2[Primary], "Y", Table2[Status], 
"Active", Table2[SeqNo], MINX(FILTER(Table2, ( Table2[Primary] =  "Y" && 
Table2[Status] = "Active" && Table2[CaseId] = Table1[CaseId])), 
Table2[SeqNo])) )

Upvotes: 1

Views: 99

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

That's quite the mess. Try this formulation:

CodeCase = 
    VAR SeqNo = CALCULATE(MIN(Table2[SeqNo]), Table2[Primary] = "Y", Table2[Status] = "Active")
    VAR Code = LOOKUPVALUE(Table2[Code], Table2[SeqNo], SeqNo)
    VAR CodeCareNo = LOOKUPVALUE(Table2[CodeCareNo], Table2[SeqNo], SeqNo)
    RETURN IF(ISBLANK(Code) || ISBLANK(CodeCareNo), BLANK(), "(" & Code & ") " & CodeCareNo)

(I like to use variables for computational efficiency and readability.)

Upvotes: 3

Related Questions