How to use lookupvalue function in Power BI?

Lookupvalue function is one of the important function in dax which allows users to get corresponding row value from another column based on row value of a column.

So lets discuss a use case in order to understand the way lookupvalue function is used in Power BI.

Problem Statement

Lets Suppose we have got three columns Date, Company and User. In this use case we will have distinct count of the user which lie in the period one greater than the selected time period. Here we have got three time period, first one is Year, second one is Quarter and third one is Month.

For example, We have selected value in month slicer as Jan 2020 then we will have to find the distinct count of user in the month of Feb 2020. Similarly, if we have selected value in Quarter slicer as Q1 2020 then we will have to show distinct count of user for Q2 2020. Similarly, if we have selected Year Slicer as 2020 then we will have to show the distinct count of user for the year 2021.

Solution

We will discuss the solution in a step by step manner for better understanding.

Step 1 : Create a dummy table

Below screenshot shows the data which has been taken for the demo purpose.

Step2 : Create calculated columns

Once the dummy table has been loaded into the power bi using an excel connector or you you can create a dummy table in power bi itself.

You can start adding calculated columns for extracting month, quarter, year, next month, next quarter and next year column using dax.

Please find below dax for each of the calculated column added in the dummy table.

Month

FORMAT(Sheet1[Date ],”mmm-yyyy”)

Format function changes the format of the column values. Here, Date column which we have created in the dummy table will be be shown in the mmm-yyyy format eg. Jan-2020.

Quarter

“Q”&QUARTER(Sheet1[Date ])&” “&YEAR(Sheet1[Date ])

We are using & to concat string and time intelligence function such as quarter and year to show the value as Q1 2020 format.

Year

YEAR(Sheet1[Date ]) Year function is used to extract year from the date function.

Next Month

Nxt Month =
IF (
MONTH ( Sheet1[Date ].[Date] ) < 12,
FORMAT ( DATEADD ( Sheet1[Date ].[Date], 1, MONTH ), “mmm” ) & “-”
& FORMAT ( Sheet1[Date ], “yyyy” ),
FORMAT ( DATEADD ( Sheet1[Date ].[Date], -11, MONTH ), “mmm” ) & “-”
& YEAR ( Sheet1[Date ].[Date] ) + 1
) In order to create next month calculated column we have used if condition so that we can handle situation where current month is December and year is current year +1.

Next Quarter

Nxt Qtr =
IF (
INT ( FORMAT ( Sheet1[Date ], “q” ) ) <= 3,
“Q”
& QUARTER ( Sheet1[Date ] ) + 1 & ” ”
& YEAR ( Sheet1[Date ] ),
“Q” & “1” & ” ”
& YEAR ( Sheet1[Date ] ) + 1
)

Similarly for finding next quarter and specifically Q4 of a particular year we have to handle the situation separately using if condition for eg. when base period will be Q4 2020 then next period will be Q1 2021. So this scenario we have handled using the above code.

Next Year

Nxt Year = YEAR(Sheet1[Date ])+1

Here, using the above code we find the next year.

Step 3: Create Measures

We need a measure which we will place in the card to show the value for next month, quarter and year based on the selected month, quarter and year.

meas =

VAR A =

LOOKUP VALUE (

Sheet1[Nxt Month],

Sheet1[Month], SELECTEDVALUE ( Sheet1[Month] )

)

VAR B =

LOOKUPVALUE ( Sheet1[Nxt Qtr], Sheet1[Qtr], SELECTEDVALUE ( Sheet1[Qtr] ) )

VAR C =

LOOKUPVALUE ( Sheet1[Nxt Year], Sheet1[Year], SELECTEDVALUE ( Sheet1[Year] ) )

RETURN

IF (

ISFILTERED ( Sheet1[Month] ),

CALCULATE ( DISTINCTCOUNT ( Sheet1[User] ), Sheet1[Month] = A ),

IF (

ISFILTERED ( Sheet1[Qtr] ),

CALCULATE ( DISTINCTCOUNT ( Sheet1[User] ), Sheet1[Qtr] = B ),

IF (

ISFILTERED ( Sheet1[Year] ),

CALCULATE ( DISTINCTCOUNT ( Sheet1[User] ), Sheet1[Year] = C ),

“”

)

In the above-mentioed DAX formula, we have used the lookup value function to fetch the value from the next month, quarter, and year column based on the month, quarter, and year value selected in the slicer.

Step 4: Create Visuals

So, we have created all the calculated columns and a measure for making visuals.

First, we have to create a hierarchical slicer in order to select the month, quarter, and year on the basis of which value from next month, quarter, and year will be shown in the card.