cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roofi00 Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

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
Community Support Team
Community Support Team

Re: Streaming database - sum value by latest date and key

Hi @roofi00 ,

 

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.

roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

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?

Community Support Team
Community Support Team

Re: Streaming database - sum value by latest date and key

Hi @roofi00 ,

 

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.

 

roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

@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

Community Support Team
Community Support Team

Re: Streaming database - sum value by latest date and key

Hi @roofi00 ,

 

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.

roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

New columns are disabled in streaming databse unfortunately..

image.pngimage.png

Super User
Super User

Re: Streaming database - sum value by latest date and key

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/
roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

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

Highlighted
roofi00 Regular Visitor
Regular Visitor

Re: Streaming database - sum value by latest date and key

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

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,597)