Forum Discussion

rjsidek's avatar
rjsidek
Icon for Helper II rankHelper II
6 years ago

Lookupvalue error = A table of multiple values was supplied when a single value was expected

Hello,

 

I currently have a dataset that looks like this

 

 

I created a measure in powerbi with the following DAX

 

 

Status of Deal Current Status = 
VAR __selectedDate = MAX ( Table1[Date of Event] )
VAR __companyName = SELECTEDVALUE(Table1[Company Name])
VAR __latestStatus = LOOKUPVALUE(Table1[Status of deal],[Date of Event],__selectedDate,[Company Name],__companyName)
RETURN
IF ( __latestStatus == BLANK() || __latestStatus = "",
    VAR __latestStatusDate = 
        CALCULATE ( 
            MAX( Table1[Date of Event] ),
            Table1[Date of Event] < __selectedDate, 
            Table1[Status of deal] <> BLANK()  
        )
    RETURN  
        CALCULATE ( 
            MAX ( Table1[Status of deal] ), 
            Table1[Date of Event] = __latestStatusDate 
        ),
__latestStatus
)

 

 

As you can see from the sample dataset, there are multiple entries for the companies. For example, there are 4 entries for the company Jaya Grocer. The above DAX is an attempt to look at the column "Status of deal" for each company, by the latest entry.

 

I encountered an error with this DAX, and I realized that the error is because there are 2 entries for max date. In the sample dataset, I highlighted that Jaya Grocer has 2 entries with the same Max Date. Hence, the error. 

 

Is there a workaround for this? I could manipulate the underlying dataset in Excel but that would be a temporary fix. Does anyone have a workaround that is dynamic? Appreciate if anyone could offer some insight.

 

Below is a link to the pbi file and excel dataset

 

https://khazanahcommy-my.sharepoint.com/:f:/g/personal/rushdi_jaafar_khazanah_com_my/EsVSSOeqwldMjTHKTpPZYuoBlXws6iz5sc8ddxaiqllTqA?e=Jtxi3h

 

Thanks 

 

 

Need Help

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    What is the error.  When i open the file, take the measure away from the visual and reinsert it, it shows the results just fine.

     

    • rjsidek's avatar
      rjsidek
      Icon for Helper II rankHelper II
      6 years ago

      Hi Ashish,

       

      I added a new entry for Jaya Grocer after the fact. If you try and refresh the dataset, PowerBI will not let you. It returns the following error:

       

      Failed to save modifications to the server. Error returned: 'MdxScript(Model) (7, 34) A table of multiple values was supplied where a single value was expected. The current operation was cancelled because another operation in the transaction failed. '.

       

      The error is not captured yet in the measure I created as Table1 is not refreshed to include the new entry for Jaya Grocer. Sorry for not clarifying this earlier