Forum Discussion

neillago's avatar
neillago
Regular Visitor
5 years ago

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.

 

 

Need Help

9 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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

    • neillago's avatar
      neillago
      Regular Visitor
      5 years ago

      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. 

    • neillago's avatar
      neillago
      Regular Visitor
      5 years ago

      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:

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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

     

    • neillago's avatar
      neillago
      Regular Visitor
      5 years ago

      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.