Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | +---------+---------+--------------------------+
Solved! Go to Solution.
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" )
Please see attached file as well
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" )
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] ) )
Please see attached file as well
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.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |