Forum Discussion

oliverblane's avatar
oliverblane
Helper III
4 years ago
Solved

Calculated Measure that Filters Another Table with Inactive Date Relationship

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

 

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!

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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
    )

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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
    )