Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
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:
[Table]
DateTime | MillNo | ErrorCode |
2/18/2021 10:00:02 AM | 01 | 12 |
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:
Hi @neillago
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.
Best Regard
Lucien Wang
Hi,
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.
Hi @neillago
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?
Regards
Phil
Proud to be a Super User!
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.
Hi @neillago
Are the error codes numbers or text?
Regards
Phil
Proud to be a Super User!
Unfortunately not.
Sorry? Which one? Are they numbers or text?
Please download this sample PBIX.
I can't reproduce your issue, it works for me. See my file and check it against yours.
Please link to your PBIX if you can't get it working.
Regards
Phil
Proud to be a Super User!
Sorry. The error codes are data type Decimal number with Whole number formatting applied.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |