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

4 Replies

  • 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.

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

    Hi, krzysj13 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    You may create a measure or a calculated column as below.

    Measure:

    Measure = 
CALCULATE(
    MAX(Table2[AlarmId]),
    FILTER(
        ALL(Table2),
        [StartTime]>=SELECTEDVALUE(Table1[Pervious])&&
        [StartTime]<=SELECTEDVALUE(Table1[Local Time])
    )
)

     

    Calculated column:

    Column = 
CALCULATE(
    MAX(Table2[AlarmId]),
    FILTER(
        ALL(Table2),
        [StartTime]>=EARLIER(Table1[Pervious])&&
        [StartTime]<=EARLIER(Table1[Local Time])
    )
)

     

    Result:

     

    Best Regards

    Allan

     

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