I am having trouble getting my measure to work. I am using DirectQuery on a streaming Power BI dataset. A sample of the data looks like this:
2/18/2021 10:00:02 AM
|2/18/2021 10:55:52 AM||52||0|
|2/18/2021 11:42:12 AM||22||58|
|2/18/2021 12:05:30 PM||38||-1|
|2/18/2021 12:12:54 PM||01||0|
I am trying to get the latest value for ErrorCode using the MAX of DateTime, which I will then filter by MillNo
Here is my Measure:
Try the following step to get what you need:
Step1,use the following dax to create the max time column:
Measure:maxtime1 = var maxtime =calculate(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[MillNo]=max('Table'[MillNo]))) return maxtime
Column: maxtime = 'Table'[maxtime1]
Step2,use the following dax to get what you want:
_lastJobError = CALCULATE(MAX('Table'[ErrorCode]),FILTER(ALL('Table'),'Table'[DateTime]='Table'[maxtime]&&'Table'[MillNo]=MAX('Table'[MillNo])))
Click here to download pbix if you need.
My problem is that I cannot create a calculated column as I am using Direct Query to get data in real time. I have to use a measure.
Have you looked at my PBIX file? I can't reproduce the issue wth the sample data you provided.
Can you double check your data for blanks/null/invalid data in the ErrorCode column?
I have run tests.
I created a new Power BI dataset and pushed 5 values to it and the calculation worked.
I created a new file using my full dataset from the original file and the issue remains. It is a streaming dataset with 4900 rows and about 200-500 added a day.
I got this error when adding the new measure to a table:
I did look. Thank you. It is still not working on my file. I sent a PM with the full file.
the data does not have blanks, Nulls or invalid data.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!