Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
106 | |
87 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |