cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neillago
Regular Visitor

Help with DAX CALCULATE returning incorrect blanks

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]

DateTimeMillNoErrorCode

2/18/2021 10:00:02 AM

0112
2/18/2021 10:55:52 AM520
2/18/2021 11:42:12 AM2258
2/18/2021 12:05:30 PM38-1
2/18/2021 12:12:54 PM010

 

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:

 

_lastJobError =

CALCULATE(
SUM(Table[ErrorCode]),
FILTER(
ALLSELECTED(Table),
Table[DateTime] = MAX(Table[DateTime])
)
)
 
I am getting blanks in return.  Please help.

 

 

9 REPLIES 9
v-luwang-msft
Community Support
Community Support

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]

v-luwang-msft_0-1613960206027.png

 

 

 

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])))

v-luwang-msft_1-1613960206029.png

 

 

 

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.

PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


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:

image.png

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. 

PhilipTreacy
Super User III
Super User III

Hi @neillago 

Are the error codes numbers or text?

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Unfortunately not. 

@neillago 

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Sorry.  The error codes are data type Decimal number with Whole number formatting applied.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors