Get values from one table and put in other table based on other column DAX/Power Query M

2
votes

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!

2
powerbidaxpowerquerypowerbi-datasourcepowerbi-desktop
What if valA is in multiple ranges of ValMin and ValMax? How do you want to treat those cases? Concatenate NameB? – StelioK
This would be very easy to pre process in SQL. – StelioK

2 Answers

4
votes

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.

1
votes

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] 
        )
    )