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.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.