Forum Discussion

emcalleja's avatar
emcalleja
Regular Visitor
6 years ago
Solved

Power Query / DAX equivalent to Excel's Index Match with Dynamic Array

Hi all I hope you all are safe and sound.

 

Hope you guys can help me, I've been struggling with this problem for a few days now.

 

I have two tables to handle raw material, one for purchases (Entries) and one for usage. I want to reference the latest cost of purchase of a material in each occurrence of the second table (usage).

 

I can handle this query in Excel with the next formula:

 

(Within the usage table)

 

 

=INDEX(Entries[Cost],MATCH([@[MovementDate]],IF(Entries[MaterialName]=[@MaterialName],Entries[MovementDate]),1))

 

 


And this formula works perfectly because it gives me the costs of purchase of that day or the one for the latest purchase for each occurrence. The thing is that I want to input those values into a Data Model and interact with them with DAX.

 

Can anyone please guide me through?

 

Thank you in advance.

  • edhans's avatar
    edhans
    6 years ago

    Ok emcalleja , try this. And I apologize if I mangle the field meanings due to them being in not English. ğŸ˜‚

    This measure works I think.

    Price = 
VAR varCurrentDate =
    MAX( Salidas[Fecha de movimiento] )
VAR varCurrentItem =
    MAX( Salidas[Nombre] )
VAR varEntradasData =
    FILTER(
        Entradas,
        Entradas[Nombre] = varCurrentItem
            && Entradas[Fecha de movimiento] <= varCurrentDate
    )
VAR varEntradasMaxDate =
    MAXX(
        varEntradasData,
        [Fecha de movimiento]
    )
VAR varPrice =
    MAXX(
        FILTER(
            varEntradasData,
            [Fecha de movimiento] = varEntradasMaxDate
        ),
        [Costo / Venta]
    )
RETURN
    varPrice

    The problem is you need a bit of modeling. See my PBIX. file. It returns this:

    Here is what I did

    1. I got all of the names (nombres?) from both tables and put them in a Names table, then removed duplicates. The problem is you have some case sensitivity issues, so I had to change case to "proper" because to Power Query, Car <> CAR <> CaR. DAX doesn't care. Power Query does.
    2. I used hte Name table as my 1-many in the relationship shown here:

      1.  

    3. Then I created and used that measure in the table visual above. I am pretty sure this can be optimized, but shoudl get you started.

     

     

6 Replies

    • emcalleja's avatar
      emcalleja
      Regular Visitor
      6 years ago

      Hi!

       

      Thank you for the fast response.

       

      The thing is that when I merge the Queries I get only the values that match exactly the given date:

       

       

      And that of course is not exactly what I want, what I want is to take "the cost of the latest purchase before the date that I used the product".

       

      What Excel does is that goes through the table and makes an approximate match, and I don't know how to acomplish this with Power Query or Dax!

       

      Here's the file with the sample data:

       

      https://www.dropbox.com/s/a45k8ej1utglahg/pq_cost.xlsx?dl=0

       

      The expected result should look like this:

       

      Purchase table:

      ProdDateCost
      A1/jan/20100
      A4/jan/20

      150

      A8/jan/20

      90

       

      Usage table

      ProdDateCost
      A2/jan/20100
      A3/jan/20

      100

      A5/jan/20

      150

      A6/jan/20

      150

      A8/jan/20

      90

      A9/jan/20

      90

       

      Thank you again, in the attached workbook I've put how it works in Excel

       

       

       

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Ok emcalleja , try this. And I apologize if I mangle the field meanings due to them being in not English. ğŸ˜‚

        This measure works I think.

        Price = 
VAR varCurrentDate =
    MAX( Salidas[Fecha de movimiento] )
VAR varCurrentItem =
    MAX( Salidas[Nombre] )
VAR varEntradasData =
    FILTER(
        Entradas,
        Entradas[Nombre] = varCurrentItem
            && Entradas[Fecha de movimiento] <= varCurrentDate
    )
VAR varEntradasMaxDate =
    MAXX(
        varEntradasData,
        [Fecha de movimiento]
    )
VAR varPrice =
    MAXX(
        FILTER(
            varEntradasData,
            [Fecha de movimiento] = varEntradasMaxDate
        ),
        [Costo / Venta]
    )
RETURN
    varPrice

        The problem is you need a bit of modeling. See my PBIX. file. It returns this:

        Here is what I did

        1. I got all of the names (nombres?) from both tables and put them in a Names table, then removed duplicates. The problem is you have some case sensitivity issues, so I had to change case to "proper" because to Power Query, Car <> CAR <> CaR. DAX doesn't care. Power Query does.
        2. I used hte Name table as my 1-many in the relationship shown here:

          1.  

        3. Then I created and used that measure in the table visual above. I am pretty sure this can be optimized, but shoudl get you started.

         

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    emcalleja - See if this helps: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

     

    Otherwise sample data and expected result would help. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.