Calculated Measure that Filters Another Table with Inactive Date Relationship

[oliverblane]

Hi, this is a follow up question this post: https://community.powerbi.com/t5/Desktop/Calculated-Measure-that-Filters-Another-Table/m-p/2454962#M...

I would like to extend the solution to handle using inactive date relationships. Table1 looks like:

and Table2 looks like:

I would like to calculate the number of distinct PRN values where Course Status = 1, and the start date is within the date slicer range. The measure that I have written for this task is:

Distinct Courses 1 - Using Other Date Relationship = 
var _1 = SUMMARIZE(FILTER(Table1, Table1[Course Status] =1), Table1[ID])
return
CALCULATE(
DISTINCTCOUNT(Table2[PRN]),
Table2,
Table2[ID] in _1,
REMOVEFILTERS(Table1[Start Date]),
USERELATIONSHIP(Table1[Start Date], DateTable[Date]))

But it does not seem to respect the inactive relationship seen in the model:

Here is a sample file: sample_file.pbix

Thanks for any help on this!

[v-easonf-msft]

Hi， @oliverblane 

Please check the attached pbix. @amitchandak ‘s formula can work for you.

If your problem has been solved, please accept this reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason

[amitchandak · Accepted solution]

@oliverblane , I check out file and tried a few versions. Can you share an example out output difference - expected vs Actual ?

example

Distinct Courses 1 - Using Other Date Relationship = 
var _1 = SUMMARIZE(FILTER(CALCULATETABLE( Table1,USERELATIONSHIP(Table1[Start Date], DateTable[Date])), Table1[Course Status] =1), Table1[ID])
return
CALCULATE(
    DISTINCTCOUNT(Table2[PRN]),
    Table2,
    Table2[ID] in _1
    )