Forum Discussion

martipe1's avatar
martipe1
Helper II
2 years ago

How to use INDEX DAX function in a calculated column?

I'm a newbie in PowerBi.

 

I have an excel report trying to convert to Power Bi, my last column in Excel file is a calculated using Index-match functions, but I can't figure it out how to convert it to a calculated column in Power BI.

My excel formula is:

=IF([@CURRENCY] = "PES",[@[AMOUNT]], [@[AMOUNT]]*INDEX(L:L,MATCH(1,(MID([@DATE],5,2)=MID(E:E,5,2))*(LEFT([@DATE],4)=LEFT(E:E,4))*(G:G="PES"),0)))

Thanks in advance for your help

 

Need Help
Show and Tell

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    martipe1 
    Please try:

    NewColumn =
IF (
    'YourTable'[CURRENCY] = "PES",
    'YourTable'[AMOUNT],
    'YourTable'[AMOUNT]
        * CALCULATE (
            SUM ( 'YourTable'[AMOUNT] ),
            FILTER (
                'YourTable',
                MID ( 'YourTable'[DATE], 5, 2 ) = MID ( 'YourTable'[DATE], 5, 2 )
                    && LEFT ( 'YourTable'[DATE], 4 ) = LEFT ( 'YourTable'[DATE], 4 )
                    && 'YourTable'[CURRENCY] = "PES"
            )
        )
)



  • martipe1's avatar
    martipe1
    Helper II
    2 years ago

    Thank you very much for your answer.

     

    What I see your answer does is multiplies the amount by the sum of all amounts where the currency equals "PES"

    What I'm attemping to do is to multiply the amount by the exchange rate (columns L:L) where the currency matches "PES" (G:G) and the month and year of that exchange rate matches the date of the amount

    Any suggestion?

    • Fowmy's avatar
      Fowmy
      Super User
      2 years ago

      martipe1 

      Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

      • martipe1's avatar
        martipe1
        Helper II
        2 years ago

        Thank you for your answer.

        I uploaded a dummy Excel and Power BI files. The excel file has the formula I want to reproduce in Power Bi

        Dummy files

         

        Thanks!!