Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HendrixSpirit
Helper I
Helper I

If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Light indicator)

Dear all, Here i come again to try to solve a simple issue.

I have 2 tables RELATED


TABLE 1

 +---------+---------+
| PRIMARY | PRODUCT |
|   KEY   |         |
+---------+---------+
| 1       | apple   |
+---------+---------+
| 2       | orange  |
+---------+---------+
| 3       | banana  |
+---------+---------+
| 4       | plum    |
+---------+---------+

Table 2

 +---------+------------+-----------+
| PRIMARY | COUNTRY OF | PRICE CIF |
|   KEY   |   ORIGIN   |           |
+---------+------------+-----------+
| 1       | CHILE      | 10        |
+---------+------------+-----------+
| 1       | ARGENTINA  | 15        |
+---------+------------+-----------+
| 1       | COLOMBIA   | 30        |
+---------+------------+-----------+
| 2       | CHILE      | 10        |
+---------+------------+-----------+
| 2       | ARGENTINA  | 20        |
+---------+------------+-----------+
| 2       | COLOMBIA   | 5         |
+---------+------------+-----------+
| 3       | CHILE      | 5         |
+---------+------------+-----------+
| 3       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 3       | COLOMBIA   | 10        |
+---------+------------+-----------+
| 4       | CHILE      | 15        |
+---------+------------+-----------+
| 4       | ARGENTINA  | 13        |
+---------+------------+-----------+
| 4       | COLOMBIA   | 20        |
+---------+------------+-----------+

What I need to create is a new column that works as a traffic light in table N°2
If CHILE is the cheaper on product x, then "GREEN LIGHT"
If CHILE is not the cheaper, then "RED LIGHT".


RESULT should be for table 2.

 

+---------+-----------+-------+----------------+
| product | provider  | price | TRAFFIC LIGHT  |
|   code  |           |       |   (NEW COLUMN) |
+---------+-----------+-------+----------------+
| 1       | CHILE     | 10    | GREEN          |
+---------+-----------+-------+----------------+
| 1       | ARGENTINA | 15    | GREEN          |
+---------+-----------+-------+----------------+
| 1       | COLOMBIA  | 30    | GREEN          |
+---------+-----------+-------+----------------+
| 2       | CHILE     | 10    | RED            |
+---------+-----------+-------+----------------+
| 2       | ARGENTINA | 20    | RED            |
+---------+-----------+-------+----------------+
| 2       | COLOMBIA  | 5     | RED            |
+---------+-----------+-------+----------------+
| 3       | CHILE     | 5     | GREEN          |
+---------+-----------+-------+----------------+
| 3       | ARGENTINA | 10    | GREEN          |
+---------+-----------+-------+----------------+
| 3       | COLOMBIA  | 10    | GREEN          |
+---------+-----------+-------+----------------+
| 4       | CHILE     | 15    | RED            |
+---------+-----------+-------+----------------+
| 4       | ARGENTINA | 13    | RED            |
+---------+-----------+-------+----------------+
| 4       | COLOMBIA  | 20    | RED            |
+---------+-----------+-------+----------------+


How can i make the "formula on DAX" to have this result on the new Column "TRAFFIC LIGHT"?

 

I've tried with "related table" with many different ways, but I can't manage to do it. This is very similar to a question I asked in the past but with a twist. http://community.powerbi.com/t5/Desktop/If-related-table-contains-quot-x-quot-return-quot-x-quot-on-...

 

Any help would be appreciated, I'm fairly new to DAX. I always get stuck in the easiest problems. 😞

 

Regards

 

 

BONUS, if you can also make the following to happen on TABLE N°1, WOULD BE MAGNIFICENT.

 

+---------+---------+--------------------------+
| PRIMARY | PRODUCT | traffic light            |
|   KEY   |         |   table n°1 (NEW COLUMN) |
+---------+---------+--------------------------+
| 1       | apple   | GREEN                    |
+---------+---------+--------------------------+
| 2       | orange  | RED                      |
+---------+---------+--------------------------+
| 3       | banana  | GREEN                    |
+---------+---------+--------------------------+
| 4       | plum    | RED                      |
+---------+---------+--------------------------+
2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@HendrixSpirit

 

Try this Column

 

Column =
VAR LowestPriceRow =
    TOPN (
        1,
        FILTER ( Table2, Table2[PRIMARY KEY] = EARLIER ( Table2[PRIMARY KEY] ) ),
        [ PRICE CIF ], ASC
    )
VAR CountryWithLowestPrice =
    MINX ( LowestPriceRow, [COUNTRY OF ORIGIN] )
RETURN
    IF ( CountryWithLowestPrice = "CHILE", "GREEN", "RED" )

Regards
Zubair

Please try my custom visuals

View solution in original post

@HendrixSpirit

 

Please see attached file as well

 

Green.pngRed.png


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@HendrixSpirit

 

Try this Column

 

Column =
VAR LowestPriceRow =
    TOPN (
        1,
        FILTER ( Table2, Table2[PRIMARY KEY] = EARLIER ( Table2[PRIMARY KEY] ) ),
        [ PRICE CIF ], ASC
    )
VAR CountryWithLowestPrice =
    MINX ( LowestPriceRow, [COUNTRY OF ORIGIN] )
RETURN
    IF ( CountryWithLowestPrice = "CHILE", "GREEN", "RED" )

Regards
Zubair

Please try my custom visuals

@HendrixSpirit

 

Then in Table 1 you can use this calculated column....referring to the calculated column you computed above

 

Column in Table 1 =
CALCULATE ( VALUES ( Table2[Column] ) )

Regards
Zubair

Please try my custom visuals

@HendrixSpirit

 

Please see attached file as well

 

Green.pngRed.png


Regards
Zubair

Please try my custom visuals

SPOT ON! Thx Zubair,

 

Now i'm going something similar, but increase the level of difficult, as the main variable of cheaper, is not in table 2, but in table 0. So it's the "other way around"

 

See the following example:

 

Table 0 

+---------+-----------------+
| PRIMARY | TYPE OF PRODUCT |
|   KEY   |                 |
+---------+-----------------+
| 1       | FRUITS          |
+---------+-----------------+
| 2       | FRUITS          |
+---------+-----------------+
| 3       | FRUITS          |
+---------+-----------------+
| 4       | VEGETABLES      |
+---------+-----------------+
| 5       | VEGETABLES      |
+---------+-----------------+
| 6       | VEGETABLES      |
+---------+-----------------+
| 7       | ANIMAL MEAT     |
+---------+-----------------+
| 8       | ANIMAL MEAT     |
+---------+-----------------+
| 9       | ANIMAL MEAT     |
+---------+-----------------+
| 10      | SEAFOOD MEAT    |
+---------+-----------------+
| 11      | SEAFOOD MEAT    |
+---------+-----------------+
| 12      | SEAFOOD MEAT    |
+---------+-----------------+

 

 

Table 1

 

+---------+----------+
| PRIMARY | PRODUCT  |
|   KEY   |          |
+---------+----------+
| 1       | ORANGE   |
+---------+----------+
| 2       | APPLE    |
+---------+----------+
| 3       | PLUM     |
+---------+----------+
| 4       | LETTUCE  |
+---------+----------+
| 5       | BROCOLI  |
+---------+----------+
| 6       | CARROT   |
+---------+----------+
| 7       | COW MEAT |
+---------+----------+
| 8       | CHICKEN  |
+---------+----------+
| 9       | TURKEY   |
+---------+----------+
| 10      | FISH     |
+---------+----------+
| 11      | MOLUSCS  |
+---------+----------+
| 12      | LOBSTER  |
+---------+----------+

 

Table 2

 

+---------+------------+-----------+
| PRIMARY | COUNTRY OF | CIF PRICE |
|   KEY   |   ORIGIN   |           |
+---------+------------+-----------+
| 1       | CHILE      | 1         |
+---------+------------+-----------+
| 1       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 2       | CHILE      | 1         |
+---------+------------+-----------+
| 2       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 3       | CHILE      | 1         |
+---------+------------+-----------+
| 3       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 4       | CHILE      | 1         |
+---------+------------+-----------+
| 4       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 5       | CHILE      | 10        |
+---------+------------+-----------+
| 5       | ARGENTINA  | 1         |
+---------+------------+-----------+
| 6       | CHILE      | 1         |
+---------+------------+-----------+
| 6       | ARGENTINA  | 10        |
+---------+------------+-----------+
| 7       | CHILE      | 7         |
+---------+------------+-----------+
| 7       | ARGENTINA  | 8         |
+---------+------------+-----------+
| 8       | CHILE      | 8         |
+---------+------------+-----------+
| 8       | ARGENTINA  | 8         |
+---------+------------+-----------+
| 9       | CHILE      | 7         |
+---------+------------+-----------+
| 9       | ARGENTINA  | 8         |
+---------+------------+-----------+
| 10      | CHILE      | 5         |
+---------+------------+-----------+
| 10      | ARGENTINA  | 8         |
+---------+------------+-----------+
| 11      | CHILE      | 5         |
+---------+------------+-----------+
| 11      | ARGENTINA  | 8         |
+---------+------------+-----------+
| 12      | CHILE      | 5         |
+---------+------------+-----------+
| 12      | ARGENTINA  | 8         |
+---------+------------+-----------+

 

What has to be calculated is:

ONLY IF CHILE IS THE CHEAPEST IN ALL PRODUCTS OF TYPE OF PRODUCT, RESULT IS GREEN ON TABLE 0, IF NOT RESULT IS RED ON TABLE 0.

 

EXPECTED RESULT?

+---------+-----------------+-----------------+
| PRIMARY | TYPE OF PRODUCT | EXPECTED RESULT |
|   KEY   |                 |   IN TABLE 0?   |
+---------+-----------------+-----------------+
| 1       | FRUITS          | GREEN           |
+---------+-----------------+-----------------+
| 2       | FRUITS          | GREEN           |
+---------+-----------------+-----------------+
| 3       | FRUITS          | GREEN           |
+---------+-----------------+-----------------+
| 4       | VEGETABLES      | RED             |
+---------+-----------------+-----------------+
| 5       | VEGETABLES      | RED             |
+---------+-----------------+-----------------+
| 6       | VEGETABLES      | RED             |
+---------+-----------------+-----------------+
| 7       | ANIMAL MEAT     | RED             |
+---------+-----------------+-----------------+
| 8       | ANIMAL MEAT     | RED             |
+---------+-----------------+-----------------+
| 9       | ANIMAL MEAT     | RED             |
+---------+-----------------+-----------------+
| 10      | SEAFOOD MEAT    | GREEN           |
+---------+-----------------+-----------------+
| 11      | SEAFOOD MEAT    | GREEN           |
+---------+-----------------+-----------------+
| 12      | SEAFOOD MEAT    | GREEN           |
+---------+-----------------+-----------------+

The results on table 1 and 2 are irrelevant.

 

@Zubair_Muhammad I hope you can help me! I'm attaching the .pbix with the tables already there, and related in the way i think they should be (0 --> 1 --> 2).

Your first response is brilliant, but its 2 --> 1, so I'm not sure how to do it the other way around!.

 

.pbix in google drive = https://drive.google.com/open?id=1Q_QMlKPo06WN4m9jKEHYHrm_UFSn2jwv

 

Regards.

 

Hi,

 

Here's the result i got.  You may download the file from here.  Please check the result thoroughly.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Deaar @Ashish_Mathur

 

Altough the calculation is right, the result is not what I was looking, because I made my question wrong (my fault)

 

The result table should be as this

 

+-----------------+----------------------------+
| Type of Product | Result expected in table 0 |
+-----------------+----------------------------+
| Fruits          | Green                      |
+-----------------+----------------------------+
| Vegetables      | Red                        |
+-----------------+----------------------------+
| Animal Meat     | Red                        |
+-----------------+----------------------------+
| Seafood Meat    | Green                      |
+-----------------+----------------------------+

 

As you are using MEASURE, the result works only If I have the PRIMARY KEY on the table... But that's not how I want it to work.

 

¿Why?

 

The idea is to use TABLE 0 as "selector" or "slicer",  and "Table 1 -2" show the results... (see new .pbix)

 

New .PBIX = https://drive.google.com/file/d/1OveW2hDl0-9no55Sk1JHbTueAXYCSWOs/view?usp=sharing

 

VIDEO EXPLAINING what I need and WHY = https://www.useloom.com/share/b02a42bd22754ec1a0679513ebf5f53a

 

Thanks you very much in advance

@Zubair_Muhammad

 

 

 

Dear Ashish,

 

Thanks for your way of approaching the problem, but it's not the answer I need, as after looking your answer, I have noticed that my question was done in the wrong way. See below post so you can see the new question.

 

 Also, I've seen you have merged all the tables to create a merged table and do the calculations there, although is a good approach, the real dataset where I'm applying this is gigantic, so I'm not sure if the best approach to this.

 

Is there a way of doing this, but without merging the tables? (Adding only a column on table 0)
@Zubair_Muhammad

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.