Use active and inactive relationship for DimDate

[Anonymous]

Hi Everyone,

Is there any way to use both active and inactive relationships in one calculated column? I have the following calculated column;

CALCULATE(COUNT(SHIPPING_LINE[KeyID]);

FILTER(CD_OTTC_SHIPPING_LINE;

AND(NOT(ISBLANK(SHIPPING_LINE[CD_OTTC_SHIPPING.STGDTE]));

AND(SHIPPING_LINE[DUEDATE].[Date]<[MAXDIMDATE];

AND(SHIPPING_LINE[CD_OTTC_SHIPPING.STGDTE].[Date]=[MAXDIMDATE];

SHIPPING_LINE[DISPATCH_DTE_2].[Date]=[MAXDIMDATE]))))

Since [MAXDIMDATE] is related to only to [DUEDATE] through the SHIPPING_LINE table and only one relationship between two tables is allowed the last two lines do not filter based on [MAXDIMDATE].

Is there any way to workaround this problem?

[amitchandak]

@Anonymous , Not very clear.

First of all Do use .date unless there is a timestamp. If it is datetime. Create a new date column and join with the date table. If you have more than one dates in one table then you need inactive relation and use relation.

refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or video -https://youtu.be/e6Y-l_JtCq4

[Anonymous]

Yes they all have time stamp hence I use .[Date]. I have more than one date column  in my table and I created one active and 2 inactive relationships between SHIPPING_LINE tabel and Date table. However, I do not know how to use "USERELATIONSHIP" within one calculate function as described above. 

[v-kelly-msft]

Hi  @Anonymous ,

Could you pls upload the relationship schema between tables?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!