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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rpinxt
Impactful Individual
Impactful Individual

Why is my last quantity formula not working

I do not understand why this is not working and returns empty value:

rpinxt_0-1683114713879.png

 

There is only 1 timestamp in so I am really surprised this is not working.

Cannot see what I am doing wrong.

2 ACCEPTED SOLUTIONS

@rpinxt CALCULATE gets wonky with single tables sometimes. Try something like below. If that doesn't work then it is quite likely that you are running afoul of auto-exist where it doesn't even try the calculation.

LastQty Measure = 
  VAR __Max_TS = MAXX(ALL('AVN PPS Daily_LC1510'),[lc_timestamp])
  VAR __Curr_TS = MAX(''AVN PPS Daily_LC1510', [lc_timestamp])
  VAR __Result = IF(__Max_TS = __Curr_TS, [Quantity], 0)
RETURN
  __Result

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler just to let you know that I got things working now.

 

rpinxt_0-1683295707859.png

As you see also your method is working.

Not entirely sure but I started over and made sure I had a pure direct query storage mode.

First I had a mixed mode because I made a Metrics table in which I stored my measures.

It is the only thing I think changed....

 

However I now made my %Done card with this formula :

rpinxt_1-1683295905333.png

 

Thanks for looking into this with me 😄

 

 

View solution in original post

14 REPLIES 14
rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler just to let you know that I got things working now.

 

rpinxt_0-1683295707859.png

As you see also your method is working.

Not entirely sure but I started over and made sure I had a pure direct query storage mode.

First I had a mixed mode because I made a Metrics table in which I stored my measures.

It is the only thing I think changed....

 

However I now made my %Done card with this formula :

rpinxt_1-1683295905333.png

 

Thanks for looking into this with me 😄

 

 

@rpinxt I wonder if the original problem was that you had a composite data model and perhaps you needed a table to be in Dual Mode but it wasn't?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
rpinxt
Impactful Individual
Impactful Individual

Not sure what that means, but could well be.

Was very mysterious to me. In the old version it would work if I would put text in the result.

But as soon as it needed to calculate or reference a measure it went blank.

rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler 
Ok with a new source the problems are still there. This is so weird:

rpinxt_0-1683191531946.png

 

Well my endgoal with this exercise is to get the %Done. To know this I need to dive the amount with status Done by the total amount.

But of course only for the last run, so the latest timestamp.

 

Even with this extensive test measure if look on every VAR as expected but the Result......

You see it points to the correct line (because all the other lines have a 0.00) but it refruses to show the Quantity (which is fine 4 columns before!)

 

I'm lost...
I put this small file on my google drive. Maybe you (or others) can see things that I am missing here...

 

Link: https://drive.google.com/file/d/1BTjiyStsCOYXODWpaOUGSlD3vV2-kPyH/view?usp=sharing

 

rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler ok with a fresh trend start still not doing as expected:

rpinxt_0-1683182742820.png

Your measure still returns nothing only the 0.

My old measure for some reason does return the last quantity of that moment.

However I would expect that on the line of 8:31 not 8:01 !! 😩

 

Very weird.

Will make a total new data source but fear that it will not make a difference...

rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler  think I have the issue....

 

This is done on a direct query database. If I switch to import :

rpinxt_0-1683121349474.png

 

**bleep**...does this mean you cannot get the latest quantity in a direct query modus??

 

Really weird because when testing all this there were instances where I got the data just fine.

The source did crash one time....maybe it corrupted things and should try with a new sql dataload.

@rpinxt What is the formula for your Quantity measure?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler well that is pretty straight forward:

Quantity = SUM('AVN PPS Daily_LC1510'[Qty])
 
Qty is a sumarized value in the direct query table:
rpinxt_0-1683123464529.png

 

@rpinxt That's really quite odd. If the issue was autoexist then I would think that would surface in both DirectQuery and import mode. Quite odd that the problem only appears in DirectQuery mode as I don't see any functions that should cause problems in DirectQuery mode.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
rpinxt
Impactful Individual
Impactful Individual

@Greg_Deckler yes it is strange.

As you see in the screens "Quantity" is just working fine so it can give amounts.

 

It is the filter where it goes wrong. Somehow it seems that it cannot compare the timestamps correctly.

As said there was a crash.

Tomorrow a new set of sql data will be generated (it resets every day).

Will see if that looks better.

 

Otherwise I will make a total new data source and see what that does.

Will post results here tomorrow.

 

rpinxt
Impactful Individual
Impactful Individual

Yes another approach with the same outcome:

rpinxt_0-1683118402837.png

The problem lies within the Filter part of the calculate, because when I take that out:

rpinxt_1-1683118469988.png

 

But I only want to see the first 3 lines because those are the latest timesstamp.

 

Do not understand why the filter is not working.

It is litteraly 2 times the same field!! lc_timestamp and max(lc-timestamp).

Makes no sense....

 

rpinxt
Impactful Individual
Impactful Individual

Bit more detail but this is really anyoing me....should work should it not??

If you do not know what the correct formula is, but still see why mine should not work please let me know!!

 

rpinxt_0-1683116967538.png

 

@rpinxt CALCULATE gets wonky with single tables sometimes. Try something like below. If that doesn't work then it is quite likely that you are running afoul of auto-exist where it doesn't even try the calculation.

LastQty Measure = 
  VAR __Max_TS = MAXX(ALL('AVN PPS Daily_LC1510'),[lc_timestamp])
  VAR __Curr_TS = MAX(''AVN PPS Daily_LC1510', [lc_timestamp])
  VAR __Result = IF(__Max_TS = __Curr_TS, [Quantity], 0)
RETURN
  __Result

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
rpinxt
Impactful Individual
Impactful Individual

Thanks @Greg_Deckler 
Tried it but gives same result:

rpinxt_0-1683120288556.png

Here "LastTime" compares lc_timestamp with LastTS.

So this proves that there is no difference in the timestamp and the max timestamp.

But still empty. Zeros are correct.

Apparently for the total it si working again...

 

But are you saying that my data is corrupted or what does "afoul of auto-exist" mean?

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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