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
Anonymous
Not applicable

Streaming database - sum value by latest date and key

Hey,

 

I'm struggling with transformation limitation regarding live datasets.

My dataset is as follow:

KeyValueDate
Key1125-09-19 12:00 PM
Key1126-09-19 13:00 PM
Key2025-09-19 12:15 PM
Key2125-09-19 12:15 PM
Key2125-09-19 12:15 PM
Key2026-09-19 13:15 PM
Key2126-09-19 13:15 PM
Key2126-09-19 13:15 PM

 

 

What I want to achieve:

By filtering by latest date that is available for each key (they can be different), I want to show aggregation of those values.

KeyValueDate
Key1126-09-19 13:00 PM
Key2226-09-19 13:15 PM

 

What I have:

But when I filter it by 'latest date', values are being aggreggated from whole dataset. 

KeyValueDate
Key1226-09-19 13:00 PM
Key2426-09-19 13:15 PM

 

I guess the main issue comes from the fact that is has also time included.

One idea that came to my mind is to aggregate those data and send them to dataset. But i would prefer to achieve this ideal state in power BI.

https://community.powerbi.com/t5/Desktop/DAX-help-Get-latest-value-for-each-ID/m-p/546375/highlight/...

Above is similar issue, but here is a need of getting sum of those values.

 

Thank you for your ideas 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello, thanks for all the involvement.

It was more than weird that it wasn't working, so I did some research and it came out that this is a product bug. 

That's a pity because we need to move to different solution.

 

Here someone reported it:

And here is my idea for fixing it, that you may upvote:

 

Regards,

Rafal

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hello, thanks for all the involvement.

It was more than weird that it wasn't working, so I did some research and it came out that this is a product bug. 

That's a pity because we need to move to different solution.

 

Here someone reported it:

And here is my idea for fixing it, that you may upvote:

 

Regards,

Rafal

Ashish_Mathur
Super User
Super User

Hi,

From your question, my guess is that you are not concerned with the time stamp.  Assuming so, i have been able to solve the question.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hey @Ashish_Mathur , thanks for the answer.

I'm afraid that live datastreams have some hidden limitations and due to those, it is impossible to work it out this way.

 

Error appeared:

image.png

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You can create measure like DAX below.

 

Value_Sum = var d=LASTNONBLANK('Table'[Date],1)
return 
CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Key]=MAX('Table'[Key])&&'Table'[Date]=d))

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks for answer @v-xicai ,

 

I guess because there are disable calculated columns (due to using streaming dataset, only calculated columns enabled) it throws a syntax error.

 

image.png

 

 

 

 

Do you think there is other option?

Hi @Anonymous ,

 

The formula above is used in measure instead of calculated column, if you need to create a calculated column , then try codes below.

 

Value_Sum =

var d=LASTNONBLANK('Table'[Date],1)

return

CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Key]=EARLIER('Table'[Key])&&'Table'[Date]=d))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

@v-xicai , hey so actually that formula doesn't throw a syntax but it shows blank values anyway 😞

 

image.pngimage.png

 

 

Those live datastreams are so annoying Smiley Mad

Hi @Anonymous ,

 

For the column formula, I have made some changes, please try to use the latest formula.

 

Value_Sum =
var d=LASTNONBLANK('Table'[Date],1)
return
CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Key]=EARLIER('Table'[Key])&&'Table'[Date]=d))

Result:

 

58.png

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

New columns are disabled in streaming databse unfortunately..

image.pngimage.png

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.