Forum Discussion
Streaming database - sum value by latest date and key
Hey,
I'm struggling with transformation limitation regarding live datasets.
My dataset is as follow:
| Key | Value | Date |
| Key1 | 1 | 25-09-19 12:00 PM |
| Key1 | 1 | 26-09-19 13:00 PM |
| Key2 | 0 | 25-09-19 12:15 PM |
| Key2 | 1 | 25-09-19 12:15 PM |
| Key2 | 1 | 25-09-19 12:15 PM |
| Key2 | 0 | 26-09-19 13:15 PM |
| Key2 | 1 | 26-09-19 13:15 PM |
| Key2 | 1 | 26-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.
| Key | Value | Date |
| Key1 | 1 | 26-09-19 13:00 PM |
| Key2 | 2 | 26-09-19 13:15 PM |
What I have:
But when I filter it by 'latest date', values are being aggreggated from whole dataset.
| Key | Value | Date |
| Key1 | 2 | 26-09-19 13:00 PM |
| Key2 | 4 | 26-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.
Above is similar issue, but here is a need of getting sum of those values.
Thank you for your ideas :)
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
9 Replies
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.
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.
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.
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.
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:
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
