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

[rjsidek]

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/EsVSSOeqwldMjTH...

Thanks 

[Ashish_Mathur]

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]

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