If "X" is cheaper in CATEGORY Z, show "Green" (Trafic Light Indicator) (.pbix file inside)

[HendrixSpirit]

Dear all.

This question is similar to If "x" is Cheaper in product Y, show "green light" that was solved spot on by @Zubair_Muhammad but the logic is backwards. Instead of going from table 2 --> 1 -- 0, it has to go from 0, to 1, to 2.

Files and tables on .PBIX.

.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

Text version of explaining what i need.

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?

TABLE 0

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                      |
+-----------------+----------------------------+

The results on table 1 and 2 are irrelevant.

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

Files and tables on .PBIX.

.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

Regards.

[Anonymous]

@HendrixSpirit, I had fun with this one. My approach is probably not optimal, but it seems to work just fine. See screenshot below. You just make the measure to check if anything is "not cheaper", then you can have a description, and a numeric measure, where you use the latter to conditionally format the former.

Is Chile Cheapest = 
VAR MinimumPrice = CALCULATE(
                    MIN('Merged dataset'[CIF Price]), 
                    FILTER('Merged dataset', 'Merged dataset'[Product] = EARLIER('Merged dataset'[Product])))
                    
VAR DuplicateOfMinimumPrice = CALCULATE(COUNTROWS('Merged dataset'), FILTER('Merged dataset', 'Merged dataset'[CIF Price] = MinimumPrice && 'Merged dataset'[Product] = EARLIER('Merged dataset'[Product]))) 

RETURN SWITCH(TRUE(),
    'Merged dataset'[Country of Origin] <> "Chile", "N/A",
    DuplicateOfMinimumPrice > 1 || MinimumPrice < 'Merged dataset'[CIF Price], "Not Cheaper",
    "Cheaper"
)

Chile Price Flag = IF(COUNTROWS(FILTER('Merged dataset', 'Merged dataset'[Is Chile Cheapest] = "Not Cheaper")) > 0, "Red", "Green")

Chile Price Flag (CF) = IF(COUNTROWS(FILTER('Merged dataset', 'Merged dataset'[Is Chile Cheapest] = "Not Cheaper")) > 0, -1, 1)

[HendrixSpirit]

dear @Anonymous good approach, this is the way i would adress the deed on an excelspreadsheet.

Can you upload the PBI? It would be great for me, as I've the PBI in spanish, and have to correct the , with ; and always make mistakes....

Regards.

[Anonymous]

@HendrixSpirit, here's a link to the PBIX file:

http://www.filedropper.com/testoflowestpriceupdated

[HendrixSpirit]

@Ashish_Mathur

You tried solving it in previous question, maybe you can help this time...

Regards

[Ashish_Mathur]

Hi,

I do not see how this question is different from the previous one.  The only change you want in my solution is that you do not want to create a Merged Table.  Am i correct?  Also, will you have a ID column on all 3 Tables or no?

[HendrixSpirit]

Dear @Ashish_Mathur

I liked your approach, and I don't have any problem on merging the tables., But it only worked if in table = appeared the primary key collumn, so It didn't work the way I expected to be, it was my error on how to do the question.

You can see what I mean here = 

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

 

‎06-26-2018 09:24 AM

Deaar ashish mathur

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

As you are using MEASURE, the result works only If I have the PRIMARY KEY appears on the table0, but I can't use it that way.

¿Why?

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

 In this video I explain why your solution is not optimum for the problem 
 https://www.useloom.com/share/b02a42bd22754ec1a0679513ebf5f53a

[HendrixSpirit]

Deaar ashish mathur

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

As you are using MEASURE, the result works only If I have the PRIMARY KEY appears on the table0, but I can't use it that way.

¿Why?

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

 In this video I explain why your solution is not optimum for the problem 
 https://www.useloom.com/share/b02a42bd22754ec1a0679513ebf5f53a

[Vvelarde]

@HendrixSpirit

Hi, interesting question, please try this calculated column:

FlagRedorGreen = 
VAR SelectCountries =
    SELECTCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                Table2,
                Table2[Primary Key],
                "MINPRICE", MIN ( Table2[CIF Price] ),
                "Country", CALCULATE (
                    CONCATENATEX (
                        VALUES ( Table2[Country of Origin] ),
                        Table2[Country of Origin],
                        "."
                    ),
                    FILTER ( Table2, Table2[CIF Price] = MIN ( Table2[CIF Price] ) )
                )
            ),
            ALLEXCEPT ( Table0, Table0[Type of Product] )
        ),
        "Country", [Country]
    )
VAR TotalCountries =
    COUNTROWS ( DISTINCT ( SelectCountries ) )
RETURN
    IF (
        TotalCountries = 1,
        IF ( FIRSTNONBLANK ( SelectCountries, [Country] ) = "Chile", "Green" ),
        "Red"
    )

Regards

Victor

[HendrixSpirit]

Dear @Vvelarde,

I tried it, and it worked on the dummy i've sent. Great

I also understand a bit of the logic you are using:

You are first creating a new "column" that appears the "country" name of the "cheapest"..

In here:

VAR SelectCountries =
    SELECTCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                Table2,
                Table2[Primary Key],
                "MINPRICE", MIN ( Table2[CIF Price] ),
                "Country", CALCULATE (
                    CONCATENATEX (
                        VALUES ( Table2[Country of Origin] ),
                        Table2[Country of Origin],
                        "."
                    ),
                    FILTER ( Table2, Table2[CIF Price] = MIN ( Table2[CIF Price] ) )
                )
            ),
            ALLEXCEPT ( Table0, Table0[Type of Product] )
        ),
        "Country", [Country]

Then someway you check if that country is CHILE or it's another country with this (I understand the IF)

VAR TotalCountries =
    COUNTROWS ( DISTINCT ( SelectCountries ) )
RETURN
    IF (
        TotalCountries = 1,
        IF ( FIRSTNONBLANK ( SelectCountries, [Country] ) = "Chile", "Green" ),
        "Red"
    )

But I've just applied this formula to the real dataset, and it didn't work. I'm going to analyze carefully what is happening tomorrow.

Can you please upload the .pbix file with your solution,, and also, explain part by part the logic behind?

I really like your solution, and I'll be happy to learn from this.

Regards,

[Vvelarde]

@HendrixSpirit

Review the PBIX.

Any doubt please contact me

Regards

Victor