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

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 



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

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 Nothing is selected in the Slicer then maximum Value and its previous value are shown in the Card.
DIY4 :  Learn to use LOOKUPVALUE DAX Function in Power BI using Example

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

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

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

First , We have to create measure to capture previous Qtr value based on selected value in the slicer. In order to achieve this we have to use LOOKUPVALUE function so that we can lookup the Qtr column with the help of Index Column.
Please find below Measure DAX Expression for capturing previous value when Qtr value is selected in the slicer.
Previous_Qtr =

    LOOKUPVALUE ( ‘Table'[Qtr], ‘Table'[Index], MAX ( ‘Table'[Index] ) – 1 )

Here , In the above expression, When Qtr column is filtered, LOOKUPVALUE function will be evaluated in DAX. LOOKUPVALUE function will return the Qtr column value for the previous Index than the maximum index in the Index Column. Since Qtr is filtered using Slicer then maximum  Index Column value will also get filtered.

Now We will write the main DAX Expression,

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!!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *