Inactive relationship in Formula (NOT in calculated function)

[Anonymous]

Dear All,

I need your help. I am trying to use inactive relationship in formula but not in calculated function. 

I have 3 tables Table1, Date Dimention Table & Table2.

I already have active relationship between Table1 & Date Dimention Table as well between Table1 & Table2. Due to this I cannot create active link between Table2 & Date Dimention Table.

Now I would like to use inactive relationship which is between Table2 & Date Dimention Table for one of the calculated measure but I can not use "USERELATIONSHIP" as I understand is only applicable for calculate function.

Below is the measure I am trying to built,

Var Table2 = TOTALMTD([NumberofID], USERELATIONSHIP (Table2[CreateDate],'Date dimension Table'[Date]))
RETURN
IF (
NOT ISBLANK (Table2),
(Table2), 0

I tried but couldn't able to attach data model diagram here.

Regards,
Abhi

[v-lili6-msft]

hi  @Anonymous 

For your data model, you have created the relationship as below:

Table2 - Table1 - Date Dimention

so, Table1 is like a bridge table, that means Table2 already has a relationship with Date Dimention, you could not create a driect relationship between Table2 and Date Dimention any more.

For your case, there two way for you refer to:

1. Make cross filter direct between Table1 and Table2 is both or Table1 ->Table2, so that you could use this Date Dimention Table to filter/Calculate with Table2. but if the relationship between Table1 and Table2 is not by Date column, that will not calculate as requirement.

2.  Make the two relationships for (Table1 & Date Dimention Table) and (Table2 & Date Dimention Table) are all Inactive.

Now when create measur, just use USERELATIONSHIP for one of two relationship to active it.

Regards,

Lin

[Fowmy · Accepted solution]

@Anonymous 

I am not sure about your model, can you try this:

Measure = 
Var Table2 = 
CALCULATE(
    TOTALMTD([NumberofID], 'Date dimension Table'[Date],),
    USERELATIONSHIP (Table2[CreateDate],'Date dimension Table'[Date])
)
RETURN
IF (
NOT ISBLANK (Table2),
(Table2), 0

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , You should be able to give it in total MTD,

Or you can use calculate on top of it.

Or use datesmtd

Var Table2 = calculate( [NumberofID],DATESMTD('Date dimension Table'[Date]), USERELATIONSHIP (Table2[CreateDate]))
RETURN
IF (
NOT ISBLANK (Table2),
(Table2), 0