Forum Discussion

krzysj13's avatar
krzysj13
Regular Visitor
5 years ago
Solved

Values between 2 dates

Hello, I'm looking the way to make new column in left table, with value from right column 'AlarmID', if its between 2 dates - LOCALTIME and Previous. Thanks alot in advance for help.    
  • DataInsights's avatar
    DataInsights
    5 years ago

    krzysj13, try this calculated column in the left table:

     

    Alarm ID =
VAR vAlarms =
    FILTER (
        Alarms,
        Alarms[Start Time] > Main[Previous]
            && Alarms[Start Time] <= Main[Local Time]
    )
VAR vResult =
    MAXX ( vAlarms, Alarms[Alarm ID] )
RETURN
    vResult

     

    Left table: Main

    Right table: Alarms

DataInsights's avatar
DataInsights
Super User
5 years ago

krzysj13, try this calculated column in the left table:

 

Alarm ID =
VAR vAlarms =
    FILTER (
        Alarms,
        Alarms[Start Time] > Main[Previous]
            && Alarms[Start Time] <= Main[Local Time]
    )
VAR vResult =
    MAXX ( vAlarms, Alarms[Alarm ID] )
RETURN
    vResult

 

Left table: Main

Right table: Alarms

  • krzysj13's avatar
    krzysj13
    Regular Visitor
    5 years ago

    Hey, thanks for the answer, but I have one more question, what if I have 2 alarms in 1 period of time?

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      5 years ago

      Hi, krzysj13 

       

      It depends on the expected result. You can choose to calculate the  aggregation of the multiple alarms for a specific period. In my dax provided above, I calculated the maximum of alarms for a period. If you want to use other aggregations, you may modify it as what you need.

       

      Best Regards

      Allan

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.