How do I countrow from 1 table meeting conditions come from another table and they are not connected

[Anonymous]

Hi,

I have 3 tables and their relationship as pic 

How do I count row in table Document when:

- Document.CaseKey = ServicePeriod.CaseKey

- Document.Date <= ServicePeriod.EndDate

- Document.Date >= ServicePeriod.StartDate

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check the measures.

Measure = 
var start_ = CALCULATE(MAX(ServicePeriod[start]),FILTER(ServicePeriod,ServicePeriod[casekey]=SELECTEDVALUE('case'[casekey])))
var end_ = CALCULATE(MAX(ServicePeriod[end]),FILTER(ServicePeriod,ServicePeriod[casekey]=SELECTEDVALUE('case'[casekey])))
return
IF(SELECTEDVALUE(document[casekey]) in VALUES(ServicePeriod[casekey])&&SELECTEDVALUE(document[date])>=start_&&SELECTEDVALUE(document[date])<=end_,1,0)

Measure 2 = SUMX(document,[Measure])

Best Regards,

Jay

[Anonymous]

Great.

Thanks a lot!

[amitchandak]

@Anonymous , Try a measure like

countx(values(case[Case key]), filter(Document, Document[CaseKey] = max(ServicePeriod[CaseKey]) && Document[Date] <= max(ServicePeriod[EndDate]) && Document[Date] >= min(ServicePeriod[StartDate])), [Case key])

[Anonymous]

Hi @amitchandak ,
I think the last [Case key] should not be there right? And without it, I got this warning "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".
Cheers