2

I need a help. I have one Table A with 2 columns - first is just some name and the other is a value. I have also Table B with 3 columns - fist with some names, second called valMin and third valMax. What I am trying to do is get the name (col1) from Table B and put into the new column in TableA based on the value in Table A, such that val from Table A is between the valMin and valMax in Table B. Example:

TableA            TableB                     Result   
|NameA|valA|     |NameB|ValMin|ValMax|      |NameA|valA|NameB|
------------     ---------------------      ------------------  
|Name1| 7  |     |BBBB |  1   |  5   |      |Name1| 7  |CCCC |  
|NAme2| 3  |     |CCCC |  6   |  10  |      |NAme2| 3  |BBBB |

Is it possible to do using DAX or Power Query M? I would appreciate your help!

Rael
  • 149
  • 2
  • 10

2 Answers2

4

Try something like this for the calculated column NameB in the Result table:

NameB =
CALCULATE (
    MAX ( TableB[NameB] ),
    FILTER (
        TableB,
        TableA[ValA] >= TableB[ValMin]
            && TableA[ValA] <= TableB[ValMax]
    )
)

When the ranges in TableB do not overlap and have no holes, the formula will allways return one value. When there are overlaps and more than one value is returned, the MAX function will prevent an error. When there are holes and no value is found, it returns a blank.

Marco Vos
  • 2,888
  • 1
  • 9
  • 10
1

Marco Russo covers this really well here: Dax Patterns: Static Segmentation

Translating the code pattern from that page to your requirement, would give something like:

[Name B] =
    CALCULATE (
        VALUES ( TableB[NameB] ),
        FILTER (
            TableB,
            TableA[valA] >= TableB[ValMin] 
            && TableA[valA] < TableB[ValMax] 
        )
    )
Olly
  • 7,749
  • 1
  • 19
  • 38