DAX (calculated column, related tables)

Question

I have a table [Delays] with a column called [Delay]. I also have a table [Ranges] with three columns: [Range], [From] and [To]. I need to create a column in the [Delays] table to display the Range value from the [Ranges] table according to this criteria: 'Delays'[Delay] >= 'Ranges'[From] && 'Delays'[Delay] < 'Ranges'[To].

Help is greatly appreciated.

You might want to create a measure that return the Min/Max range value over an arbitrary set of rows, then set you calculated column to be the value of the measure. The measure will then be evaluated in the row context of the specific row and you should get what you want. Haven't tried it yourself ;) — Lukasz P.

MyLittleDax MyLittleDax · Accepted Answer · 2015-09-22T15:19:52

Try creating a measure using:

 =CALCULATE(
   VALUES(Ranges[Range]), 
   FILTER(Ranges, 
      Delays[Delay] >= Ranges[From] 
      && Delays[Delay] < Ranges[To]
)

Depending on your table, you may need to use the HASONEVALUE function because if delay falls in multiple ranges, the measure will fail.

This is assuming that the Ranges table and Delays table are not joined together. If they are, you can just add the Ranges[Range] from the Delays table.

SUMMARIZE(
   Delays,
   Delays[Delay],
   Range[Ranges]
)

DAX (calculated column, related tables)

1 Answers