How do I say Index Match Match in Power BI?

[Anonymous]

Hi Everybody!

I’m back with another question.

Part of the data I’m using in my model is in the form of a table that is about 600 lines by 30 columns.

Going back to my fruit example, let’s say this is what I have:

Table: Days for Item to be Delivered

STORE       || Apples || Strawberries || Grapes || Green Apples || Pears || Cantaloupes || Pineapples

Uptown      ||   5       ||          6          ||     7      ||         2             ||  1      ||         10          ||     20          

Downtown ||   2       ||          8          ||     13    ||         3             ||  7      ||         13          ||     18          

Suburbs     ||   3       ||          14        ||     1      ||         4             ||  4      ||         14          ||     12          

Table: Units of Items at Each Store

STORE       || ITEM               || UNITS               || Wanted Results: Days until Delivery

Uptown     || Apples            ||     7                   ||       5

Uptown     || Strawberries   ||   9                     ||       6

Uptown     || Grapes            ||   4                     ||       7

Downtown || Apples            ||     5                  ||       2

Downtown || Green Apples ||     3                   ||       3

Downtown || Pears              ||     10                 ||       7

Suburbs     || Green Apples ||     1                   ||       4

Suburbs     || Cantaloupes   ||   6                     ||       14

Suburbs     || Pineapples      ||     2                   ||       12

If I was using Excel I’d use an INDEX MATCH MATCH function to get the Days until Delivery number. It would be extremely nice to be able to do this look up without altering the Days for Item to be Delivered table.

What is a good way of getting the Days until Delivery number?

Thanks so much!

[Ashish_Mathur]

Hi,

I will have to restructure the first dataset into a 3 column one (just like the second one) to solve this problem.  If you are OK with that, post back.

[Anonymous]

Hi Ashish!

Restructuring isn't something I really want to do but if it's the way of dealing with this issues then I'm all for it. 

Is the solution making the Days for Item to be Delivered table look like this?

STORE        ||  ITEM              ||    Days

Uptown      || Apples            ||     5

Uptown      || Strawberries   ||     6

Uptown      || Grapes            ||     7

Uptown      || Green Apples  ||    2

Uptown      || Pears               ||    1

Uptown      || Cantaloupes    ||    10

Uptown      || Pineapples      ||     20

Downtown || Apples            ||     2

Downtown || Strawberries   ||    8

Downtown || Grapes            ||     13

Downtown || Green Apples  ||    3

Downtown || Pears               ||    7

Downtown || Cantaloupes    ||    13

Downtown || Pineapples      ||     18

Suburb       || Apples            ||     3

Suburb       || Strawberries   ||     14

Suburb       || Grapes            ||     1

Suburb       || Green Apples  ||    4

Suburb       || Pears               ||    4

Suburb       || Cantaloupes    ||    14

Suburb       || Pineapples      ||     12

And then relating this new table to the Units of Items at Each Store table so I can use the Related function to find the numbers for the Wated Results: Days until Delivery?

Or am I barking up the wrong tree?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You are mostly there.  From both tables, you will have two create two more table - one with unique entries of Stores and another with unique values of Items.  Create a relationshipof each source data table to these two new tables.  In your visual, drag Item and Stores from the newly created tables.  You may thereafter write simple SUM() formulas.

[Anonymous]

Hi Ashish,

That'll work! 

Thanks so much for all of your help. I'm sure I'll be back for more advice on this model.

Cheers!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Does anybody have any ideas on how to approach this problem?