Forum Discussion

hakimissimo's avatar
hakimissimo
Regular Visitor
9 years ago
Solved

Comparing two dates from different tables

Hello,   Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.   two tables : incidents and incident-sla   The tables have a relationship based on incident ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    9 years ago

    upload to onedrive and include a link

  • richbenmintz's avatar
    richbenmintz
    9 years ago

    Hi hakimissimo,

     

    I have fixed your Calc for you and uploaded a new file to the share you created.

     

    Steps:

    - Change relationship direction from 'both' to 'single'

    - updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].

     

     

    BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))

    - Add a Table visual that show how the max value is returned for each rown in the header and detail tables

     

hakimissimo's avatar
hakimissimo
Regular Visitor
9 years ago

Hello,

 

Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.

 

two tables : incidents and incident-sla

 

The tables have a relationship based on incident number (Number).

 

What i tryed with add a new column : Column = DATEDIFF(Incidents[Opened];'Incident-sla'[start time];DAY)

 

but i have the error : a single value for column 'start time' in table incident-sla cannot be determined....

 

Any help appreciated :-)

 

++

 

 

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi hakimissimo

     

    In calculated column, we should use function “Related” if the “Start time” in the 1-side of the relationship (1:1, 1:* or *:1). Please check it out and try this formula.

     

    Column =
DATEDIFF ( Incidents[Opened]; RELATED ( 'Incident-sla'[start time] ); DAY )

      

     

    Best Regards!

    Dale

    • hakimissimo's avatar
      hakimissimo
      Regular Visitor
      9 years ago

      Hi Dale,

       

      Thank you for quick feedback, it's really appreciated.

       

      I have modified my request as you suggested.

       

      Now i have the error : 'incident-sla[Start time]' either does not exist or does'nt have the relationshipe to any table...."

       

      I have cheched the relationship and there is a many to 1 (please see attached image)... any ideas? 

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        9 years ago

        The related function relates the many side to the side, if you create your column in the incident-sla table your function will work something like

         

        Column =
DATEDIFF ( 'Incident-sla'[start time], RELATED ( Incidents[Opened] ), DAY )

        It does not really make sense to create the column on the one side on the relationship as you would not know which of the many [start time] values to use for the date diff to the [opened]. 

         

        If you are trying to use the max or min value from the many side to get the last/first value on the one side, then you could use the lookupvalue function within a calculate function function.

         

        Hope that helps