Need DAX /PowerPivot version of selecting values / Cost from various countries in certain order.

[nikkollai]

Hello Everyone,




I have a difficulty figuring this one.

I have a dataset with product costs in different countries and the objective is to create a measure or calculated column to pick cost from particular country in this order:


First pick costfrom Mexico, if no cost in Mexico - pick cost from USA, in case there is no cost in USA, pick cost from Canada.

The idea is to follow this order. Cost value is not important so using MAX over columns is not the goal here.



Accomplishing this task with excel formulas is pretty easy. Below is the first solution that came into my head and I am sure there are tons more if you really think about it. However, recreating this in DAX is turning to be a challenge. If you have any ideas how to do this in DAX or any other ideas how to come close to the solution please share your thoughts.


This is a simple excel formula =IF(ISNUMBER(H3),H3,IF(ISNUMBER(I3),I3,G3))


Below are two tables: left is the dataset and on the right is the pivot table and the final result i am looking for in DAX (column FORMULA (in red)

Here is the link for excel spreadsheet: https://1drv.ms/x/s!AtVaiaKuHugAoijOlqDQtPqCnP1o

ProductID	Country	Cost
1	USA	2.15		Row Labels	CANADA	MEXICO	USA	FORMULA
Product 1	CANADA	2.37		Product 3	2.11			2.11	=IF(ISNUMBER(G3),G3,IF(ISNUMBER(H3),H3,F3))
Product 2	USA	3.16		Product 1	2.37		2.15	2.15
Product 2	CANADA	3.16		Product 10	3.95	1.58	2.37	1.58
Product 2	MEXICO	3.16		Product 11	1.58	3.95	3.16	3.95
Product 3	CANADA	2.11		Product 12	2.84	1.99		1.99
Product 4	USA	3.16		Product 2	3.16	3.16	3.16	3.16
Product 4	CANADA	3.95		Product 4	3.95	3.16	3.16	3.16
Product 4	MEXICO	3.16		Product 5	1.58		3.95	3.95
Product 5	USA	3.95		Product 6			2.37	2.37
Product 5	CANADA	1.58		Product 7	3.16	2.37	3.16	2.37
Product 6	USA	2.37		Product 8	1.58	3.95		3.95
Product 7	USA	3.16		Product 9			3.16	3.16
Product 7	CANADA	3.16
Product 7	MEXICO	2.37
Product 8	CANADA	1.58
Product 8	MEXICO	3.95
Product 9	USA	3.16
Product 10	USA	2.37
Product 10	CANADA	3.95
Product 10	MEXICO	1.58
Product 11	USA	3.16
Product 11	CANADA	1.58
Product 11	MEXICO	3.95
Product 12	CANADA	2.84
Product 12	MEXICO	1.99

<colgroup><col><col><col><col><col><col><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>

 

[analyst44]

This is not an answer in DAX, but given I have an outstanding question here that has similarities, I am exploring workarounds to get what I want to get done. Unfortunately, this involves Excel as a middle-man, if you can use it.

I took your original pivot and formula and added two columns to it:

ProductID	Country	Final Cost
Product 3	CANADA	2.11
Product 1	USA	2.15
Product 10	MEXICO	1.58
Product 11	MEXICO	3.95
Product 12	MEXICO	1.99
Product 2	MEXICO	3.16
Product 4	MEXICO	3.16
Product 5	USA	3.95
Product 6	USA	2.37
Product 7	MEXICO	2.37
Product 8	MEXICO	3.95
Product 9	USA	3.16

<tbody>
</tbody>

Now, *this* is used as your main table in the PowerPivot Window to do further analysis or to link up by key (ProductID, Country, etc) based on whatever else you want. Again, I know this doesn't solve the problem with DAX and it may be prohibitive for you, but that's the easiest thing I could think of as I explore my issue as well. It seems like DAX would require something much more complicated in both of our cases. I originally linked this table above with your original table, but then wondered if that was even necessary.

 

[Ozeroth]

Hi nikkollai,

I've uploaded one way to do it (Excel 2016 format - let me know if you need 2010 format).
https://www.dropbox.com/s/j5nh5e51hw54m27/DAX - cost across countries - Edit.xlsx?dl=1

My suggestion is to create a Country table that has two columns: Country & Order.
Then relate your ProductCost table to Country.

Then a [Cost First] measure like this should do the trick:

Cost First
=
CALCULATE (
    MIN ( ProductCost[Cost] ),
    CALCULATETABLE (
        FIRSTNONBLANK ( Country[Order], 0 ),
        ProductCost
    )
)

The CALCULATETABLE(...) finds the earliest Country based on Order, within the context of the extended ProductCost table (so just includes countries relating to currently selected Products).

 

[nikkollai]

Ozeroth,

This is amazing! Thank you very much

N -

 

[nikkollai]

Hi Owen,
Your approach is pretty cool and it works like a charm. Is there any chance you could explain how it works. That would be great to understand the logic here.
Thank you very much
n-

 

[Ozeroth]

Hi Nikkollai,

Sure!

First of all, I have created a 'lookup' table for Country, in this case for the purpose of specifying each Country's Order. This table is related to the ProductCost table on the Country column, with the Country table being on the 1-side of a 1:many relationship.

Now, the DAX code itself is designed to filter down to the 'first' Country according to the Order column in the Country table.

Taking the code one piece at a time (from inside outwards):

1. FIRSTNONBLANK( Country[Order], 0 ) returns the minimum value of Country[Order], as a 1-row 1-column table
   (see Alternative use of FIRSTNONBLANK and LASTNONBLANK - SQLBI for explanation of this use of FIRSTNONBLANK).
   The reason for using FIRSTNONBLANK rather than MIN is that the table returned by FIRSTNONBLANK can be used for filtering, while the scalar returned by MIN can't.
2. If we didn't do anything else, the FIRSTNONBLANK would always return the Order corresponding to the first Country overall (i.e. Mexico), assuming we only filtered on columns of the ProductCost table. This is because filters on the table on the many-side of a relationship (ProductCost) do not filter the table on the 1-side of the relationship (Country).
3. However, the FIRSTNONBLANK is wrapped in CALCULATETABLE ( FIRSTNONBLANK ( Country[Order], 0 ), ProductCost )
   This has the effect of calculating the FIRSTNONBLANK in the context of the currently filtered ProductCost table. But when a table like ProductCost is provided as a SetFilter argument to CALCULATE, DAX actually interprets this as the extended version of table. The extended table includes all related tables on the 1-side of any relationships with the specified table as though the specified table were left-outer-joined with those related tables.
   In the case of ProductCost, this means that ProductCost is actually "ProductCost left-outer-join Country", so you get the Order column from the Country table as well. So DAX says, take the currently filtered rows of ProductCost (for whatever product(s) are currently selected), join the Country Order column from the Country table (which will just be for Countries that relate to the selected product(s)), and use that as the modified context to evaluate the 'minimum' Country Order.
   
   So the output of the CALCULATETABLE is the 'minimum' Country Order for currently selected products, as a 1-row 1-column table.
4. Then the outer CALCULATE( MIN(ProductCost[Cost])...) is just calculating the Product Cost in the context of the minimum Country Order. MIN is used as an arbitrary aggregation, and doesn't matter for a single product...you could change it to AVERAGE or something else depending how you want multiple products to be handled.
5. So to summarise it all, we find the minimum Country Order for currently selected product(s), and apply that as an additional filter to calculate Product Cost just for that Country corresponding to the minimum Order.

For the left-outer-join and extended table stuff in step 3, this is a more detailed explanation:
MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering

As an aside, the Country Order could have been included in the ProductCost table itself (which would have simplified the DAX), but I thought it was tidier to have it in a separate table.

All the best,
Owen

 

[nikkollai]

Ozeroth

I must have missed your reply with explanation on DAX code for this case. I terribly apologize for a delayed " THANK YOU, SIR!!! THANK YOU VERY MUCH" I read it a few times and indeed you are right without understanding a concept of extended and base tables it's difficult to understand the logic behind your code. This is a very educational explanation and "blogworthy" in my opinion in case if you are running one.
Thank you again.

N -

 

[nikkollai]

analyst44

Thank you very much for your explanation.

This is not an answer in DAX, but given I have an outstanding question here that has similarities, I am exploring workarounds to get what I want to get done. Unfortunately, this involves Excel as a middle-man, if you can use it.

I took your original pivot and formula and added two columns to it:

ProductID	Country	Final Cost
Product 3	CANADA	2.11
Product 1	USA	2.15
Product 10	MEXICO	1.58
Product 11	MEXICO	3.95
Product 12	MEXICO	1.99
Product 2	MEXICO	3.16
Product 4	MEXICO	3.16
Product 5	USA	3.95
Product 6	USA	2.37
Product 7	MEXICO	2.37
Product 8	MEXICO	3.95
Product 9	USA	3.16

<tbody>
</tbody>

Now, *this* is used as your main table in the PowerPivot Window to do further analysis or to link up by key (ProductID, Country, etc) based on whatever else you want. Again, I know this doesn't solve the problem with DAX and it may be prohibitive for you, but that's the easiest thing I could think of as I explore my issue as well. It seems like DAX would require something much more complicated in both of our cases. I originally linked this table above with your original table, but then wondered if that was even necessary.