cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
HendrixSpirit Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

@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" )

View solution in original post

Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

@HendrixSpirit

 

Please see attached file as well

 

Green.pngRed.png

View solution in original post

7 REPLIES 7
Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

@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" )

View solution in original post

Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

@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] ) )
Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

@HendrixSpirit

 

Please see attached file as well

 

Green.pngRed.png

View solution in original post

HendrixSpirit Regular Visitor
Regular Visitor

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

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.

 

Super User
Super User

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

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/
HendrixSpirit Regular Visitor
Regular Visitor

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

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

HendrixSpirit Regular Visitor
Regular Visitor

Re: If "X" is cheaper in product "Y", show "GREEN LIGHT " (Traffic Lig

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

 

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,819 guests
Please welcome our newest community members: