datediff with userelationship

[Anonymous]

I need to change this calculated column to use an inactive relationship between my date table, and the date mentioned below.  Previously, I had this as an "active" relationship but have since made it inactive.   Having a hard time getting userelationship to work but I know that a Calculate statement may need to be included somewhere.  Any suggestions?   

Leadtime Rules =

VAR _Days = DATEDIFF(

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c],

TODAY (),

DAY

)

RETURN

IF (

[Kumail]

Hello 

If you could send a sample .pbix that demonstrates what you are looking to get. It would really help to provide you with a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards

Kumail Raza

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

Here is the full code, but I cannot share the file.    Actually the formula is working.  

However, when I try to create a "group" from this column, I am getting a circular reference error and I need this group for something else.   The circular error reference is referencing the SF_Opportunity Line table, which is not related directly to the Date Table  (the Date Table is directly related to the SF_Opportunity table, and the Opportunity Line table is related to the Opportunity table).  

Leadtime Rules =

VAR _Days = DATEDIFF(

'Sf_Opportunity Line'[Customer_Requested_Ship_Date__c],

TODAY (),

DAY

)

RETURN

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -15

&& _Days > -1400,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -8

&& _Days >= -14,

"Danger Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= 0

&& _Days >= -7,

"No Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 40000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days > 0

&& _Days >= -7,

"Past Due",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -50

&& _Days > -1400,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -36

&& _Days >= -49,

"Danger Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= 0

&& _Days >= -35,

"No Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] <= 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days > 0,

"Past Due",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= 0

&& _Days > -1000,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -50

&& _Days >= -63,

"Danger Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days >= -49,

"No Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] > 250000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days > 0,

"Past Due",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -78

&& _Days > -1000,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -64

&& _Days >= -77,

"Danger Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -63,

"No Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days > 0,

"Past Due",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 1500000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -92

&& _Days > -1000,

"Upcoming Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 1500000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days <= -78

&& _Days >= -91,

"Danger Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days >= -77,

"No Zone",

IF (

'Sf_Opportunity Line'[Opp Line Sum of Total Price] >= 750000

&& 'Sf_Opportunity Line'[Status__c] = "Open"

&& _Days > 0,

"Past Due","Error")

)))))))))))))))))))

[Kumail]

You can use USERELATIONSHIP() dax function to activate inactive relationship when you have multiple relationships between table in your model.

In order to have a working solution, you may need to send the .pbix file if the above still doesn't solve your problem.

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly!!

[Kumail]

You can use USERELATIONSHIP() dax function to activate inactive relationship when you have multiple relationships between table in your model.

In order to have a working solution, you may need to send the .pbix file if the above still doesn't solve your problem.

Regards

Kumail Raza

Did this help? Kudos are appreciated.

Consider Accept it as the solution to help the other members find it more quickly!!

[Anonymous]

@Kumail  @amitchandak   The expected output is the result of the IF statements in the formula.  All I'm needing is to know how to incorporate UseRelationship into my VAR statement.   I'm thinking it might be the cause of the circular error that I am getting when I try to create a group from this calculated group.  

Previously my date table was connected directly to the Opportunity Line table, and now it is connected to the Opportunity table  (the header table for Opportunity).   

So, my idea was to try and utilize UseRelationship in my VAR statement to see if that helped.