CODEWITHSUNDEEP
powerbidax
Jelle Hoekstra
Jelle Hoekstra

Reputation: 672

Create column to classify rows based on realted tables DAX PowerBI

I have simplified my problem to solve. Lets suppose I have three tables. One containing data and specific codes that identify objects lets say Apples.

+-------------+------------+-----------+
| Data picked | Color code | Size code |
+-------------+------------+-----------+
| 1-8-2018    |          1 |         1 |
| 1-8-2018    |          1 |         3 |
| 1-8-2018    |          2 |         2 |
| 1-8-2018    |          2 |         3 |
| 1-8-2018    |          2 |         2 |
| 1-8-2018    |          3 |         3 |
| 1-8-2018    |          4 |         1 |
| 1-8-2018    |          4 |         1 |
| 1-8-2018    |          5 |         3 |
| 1-8-2018    |          6 |         1 |
| 1-8-2018    |          6 |         2 |
| 1-8-2018    |          6 |         2 |
+-------------+------------+-----------+

And i have two related helping tables to help understand the codes (their relationships are inactive in the model due to ambiguity with other tables in the real case).

+-----------+--------+
| Size code |  Size  |
+-----------+--------+
|         1 | Small  |
|         2 | Medium |
|         3 | Large  |
+-----------+--------+

and 

+------------+----------------+-------+
| Color code | Color specific | Color |
+------------+----------------+-------+
|          1 | Light green    | Green |
|          2 | Green          | Green |
|          3 | Semi green     | Green |
|          4 | Red            | Red   |
|          5 | Dark           | Red   |
|          6 | Pink           | Red   |
+------------+----------------+-------+

Lets say that I want to create an extra column in the original table to determine which apples are class A and class B given that medium green Apples are class A and large Red apples are class B, the other remain blank as the example below.

+-------------+------------+-----------+-------+
| Data picked | Color code | Size code | Class |
+-------------+------------+-----------+-------+
| 1-8-2018    |          1 |         1 |       |
| 1-8-2018    |          1 |         3 |       |
| 1-8-2018    |          2 |         2 | A     |
| 1-8-2018    |          2 |         3 |       |
| 1-8-2018    |          2 |         2 | A     |
| 1-8-2018    |          3 |         3 |       |
| 1-8-2018    |          4 |         1 |       |
| 1-8-2018    |          4 |         1 |       |
| 1-8-2018    |          5 |         3 | B     |
| 1-8-2018    |          6 |         1 |       |
| 1-8-2018    |          6 |         2 |       |
| 1-8-2018    |          6 |         2 |       |
+-------------+------------+-----------+-------+

What's the proper DAX to use given the relationships are initially inactive. Preferably solvable without creating any further additional columns in any table. I already tried codes like:

CALCULATE ( 
    "A" ; 
    FILTER ( 'Size Table' ; 'Size Table'[Size] = "Medium");
    FILTER ( 'Color Table' ; 'Color Table'[Color] = "Green")
)

And many variations on the same principle

Upvotes: 1

Views: 175

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40304

Given that the relationships are inactive, I'd suggest using LOOKUPVALUE to match ID values on the other tables. You should be able to create a calculated column as follows:

Class = 
    VAR Size = LOOKUPVALUE('Size Table'[Size],
                   'Size Table'[Size code], 'Data Table'[Size code])
    VAR Color = LOOKUPVALUE('Color Table'[Color],
                   'Color Table'[Color code], 'Data Table'[Color code])
    RETURN SWITCH(TRUE(),
               (Size = "Medium") && (Color = "Green"), "A",
               (Size = "Large") && (Color = "Red"), "B", BLANK())

If your relationships are active, then you don't need the lookups:

Class = SWITCH(TRUE(),
            (RELATED('Size Table'[Size]) = "Medium") &&
            (RELATED('Color Table'[Color]) = "Green"),
            "A",
            (RELATED('Size Table'[Size]) = "Large") &&
            (RELATED('Color Table'[Color]) = "Red"),
            "B", 
            BLANK())

Or a bit more elegantly written (especially for more classes):

Class = 
    VAR SizeColor = RELATED('Size Table'[Size]) & " " & RELATED('Color Table'[Color])
    RETURN SWITCH(TRUE(),
               SizeColor = "Medium Green", "A",
               SizeColor = "Large Red", "B",
               BLANK())

Upvotes: 1

Related Questions