Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Thanks
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.
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
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |