DATEDIFF and userelationship (inactive relation)

[Anonymous]

Hello to everyone,

I have a problem that seems unsolvable.

I want to calculate DATEDIFF between two columns BUT I need to graph these numbers by creation date (which is not the active relationship). So i tried this (and lots of other things) :

Colonne = calculate(DATEDIFF(TOP[A],TOP[B],hour)/24,USERELATIONSHIP(Calendrier[Date],TOP[ticket_creationdate]))

but I have this error message (Il est impossible de déterminer une valeur unique pour la colonne «A» de la table «TOP»...).

Exemple of my datas :

- I have an inactive relation between Calendrier[Date] and TOP[ticket_creationdate]

- I also have an active relation between Calendrier[Date] and TOP[ticket_restorationdate]

Can you help me ?

Thank's

Victoria

[Mikelytics]

Hi @Anonymous 

If you want to build this in DAX I think you will create unnecessary complexity. A solution might be something like this:

Measure = 
CALCULATE(
   SUMX(
      TOP,
      [B]-[A]
   )
   USERELATIONSHIP(Calendrier[Date],TOP[ticket_creationdate])
)

or [A] and [B] switched

Measure = 
CALCULATE(
   SUMX(
      TOP,
      [A]-[B]
   )
   USERELATIONSHIP(Calendrier[Date],TOP[ticket_creationdate])
)

Why do you not calculate the difference in Power Query first and then Use DAX with USERELATIONSHIP on the newly createx column?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Anonymous]

First of all, thank you for your feedback. Indeed, I did this formula in the first place, but for the need of a graph I need to have a calculated field with the creation date, otherwise the graph is wrong.