DIY4 : Learn to use LOOKUPVALUE DAX Function in Power BI using Example

I was solving a problem on Microsoft Power BI Community which proved to be the best use case for LOOKUPVALUE function in PowerBI. So finally, I decided that I will write a blog about it so that my readers understand where and how to use the LOOKUP VALUE Function in PowerBI.

What is the LOOKUPVALUE function in DAX Language? LOOKUPVALUE function is a very useful function in DAX as it helps to find the value of a column based on another column value in a table. You can easily relate it to the LOOKUP function in Excel.

Read more:  LOOKUPVALUE  Function Microsoft Docs

Let`s learn LOOKUPVALUE function in DAX language with the help of an example. The Whole Purpose of writing this article is to help readers understand the function and practice along with me so that they get along with me and get hands-on experience of using LOOKUPVALUE function.

Let`s Begin.

Problem Statement 

Qtr column has to be placed in the slicer  and if the user selects any value from the slicer then that value and its previous value should be shown in the Card.

If nothing is selected in the slicer then maximum Qtr value and its previous value should be shown in the Card.

Result Preview

When any value is selected in the Slicer then that value and its previous value is shown in the Card.

Hope the requirement is clear to all. Its a very good use case for using LOOKUPVALUE function.

So let`s see the solution to the problem but I would request you to please try to solve the scenario by yourself first, if you find it difficult then you can see the solution here.

Solution

select_measure =
IF (
    ISFILTERED ( ‘Table'[Qtr] ),
    MAX ( ‘Table'[Qtr] ) & ” & “ & [Previous_Qtr],
    LOOKUPVALUE ( ‘Table'[Qtr], ‘Table'[Index], MAX ( ‘Table'[Index] ) ) & ” & “
        & [Previous_Qtr]
)

In the above DAX Expression, If the slicer is filtered then

MAX ( ‘Table'[Qtr] ) & ” & “ & [Previous_Qtr]

expression will be evaluated, so after filtration only one row will left in the table MAX ( ‘Table'[Qtr] ) will capture the selected Qtr Column value and previous Qtr Column value will come from measure [Previous_Qtr].

When nothing is selected in the Slicer then LOOKUPVALUE ( ‘Table'[Qtr], ‘Table'[Index], MAX ( ‘Table'[Index] ) )  expression will give maximum Qtr Column value and [Previous_Qtr] will give the previous Qtr value.

MAX ( ‘Table'[Index] ) will be changing based on condition and the LOOKUPVALUE function will fetch us the Qtr Column value accordingly.

Conclusion

We learned about the LOOKUPVALUE function with the help of a Practical Scenario. I Hope, it was useful to all the people trying to learn PowerBI and DAX.

If you like this article then please share and comment. You can also provide your feedback to me.

Cheers!!