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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 


Follow on LinkedIn
@ 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?


Follow on LinkedIn
@ 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?


Follow on LinkedIn
@ 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.


Follow on LinkedIn
@ 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

 


Follow on LinkedIn
@ 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors