Combining 2 Lookupvalue statements in an IF Statement to create a calculated column in DAx

[Katiek]

Hi All, 

I need help with an IF statement and 2 lookupvalues in a dax calculated column.

I'm trying to combine 2 lookup statements in an IF statement. My original idea was to use VAR Ticket to look up the ticket place from another table by comparing the Date and the ticket Number. This works but then I realised that, sometimes the date could be entered incorrectly. That's why I created VAR TicketDateBefore, in case the date could not be found in Tickettbl,  to look up one day before the original ticket date from the anomally table. Both Lookup formulas work great separately but now I'm trying to combine them so I have the value in just 1 column.

I tried the following:

VAR Ticket = LOOKUPVALUE(Tickettbl[Place], Tickettbl[Date], Anomallytbl[Date], Tickettbl[TicketNumber], Anomallytbl[TicketNumber]

VAR TicketDateBefore = LOOKUPVALUE(Tickettbl[Place], Ticket[Date], Anomallytbl[Date]-1, Tickettbl[TicketNumber], Anomallytbl[TicketNumber]

RETURN

IF(Ticket="",TicketDateBefore,Ticket)

But I get a warning a Table with more values was given back, although only one value was expected.

What am I doing wrong?

Thanks in advance, 

KatieK

[Rupak_bi]

Use Max of value. 

IF(Ticket=blank(),max(TicketDateBefore),max(Ticket))

[Katiek]

Thanks @Rupak_bi.

I tried your suggestion but I got the following message "With the Max function, only one column reference is accepted as argument". I then tried only using one Max and that didn't work either.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from @Rupak_bi, please allow me to provide another insight:
Hi,@Katiek 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.When I try to reproduce your results, I get the following error:

3.Then I go ahead and create a new table named "Ticket" and it still gets the following error:

4.Then I changed the variable to ticket1, and his result looked like this:

5.So based on the above tests, I recommend checking that your calculation column is written correctly. Here is my calculation column:

Column = 

VAR Ticket1 = LOOKUPVALUE(Tickettbl[Place], Tickettbl[Date], Anomallytbl[Date], Tickettbl[TicketNumber], Anomallytbl[TicketNumber])

VAR TicketDateBefore = LOOKUPVALUE(Tickettbl[Place], Tickettbl[Date], Anomallytbl[Date]-1, Tickettbl[TicketNumber], Anomallytbl[TicketNumber])

 

RETURN

IF(Ticket1="",TicketDateBefore,Ticket1)

6.Here's my final result, which I hope meets your requirements.

7.Here is the relevant documentation to help you:

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

8.You can also try to check whether the list relationship affects your output, here is the relevant documentation:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

9.Finally, this is the version number of the desktop I am using, if there are still problems, you can update the version according to your needs:

Here is the download link for the latest version:

Download Microsoft Power BI Desktop from Official Microsoft Download Center

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

• Plz share sample data