Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.