Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a visualization which shows the cumulative contributions to a portfolio over time and its market value at given dates.
I'm connecting to a MySQL database. I initiate the connection normally by clicking New Source, entering the database credentials and my query, which includes a user-defined variable, @csum. It appears that PBI does not recognize the user defined variable as it is "outside the session." When I try to run the below, I receive the error, "MySQL: Fatal error encountered during command execution."
set @csum := 0;
select t.*, p.client_id, p.code, p.report_name1, if(t.type = 'WITHDR' or t.type = 'DELIV', -t.net_amount, t.net_amount) as Settlement_Amount, (select (@csum := @csum + Settlement_amount)) as cumulative_sum
from transaction t join portfolio p on t.portfolio_id = p.id
where t.trade_date > '2013-12-31' and (t.type = 'CONTR' OR t.type = 'WITHDR' OR t.type = 'ADD' OR t.type = 'DELIV') and t.sub_code <> 'ME' and p.code = 'REDACTED'
ORDER BY t.transaction_number
Does anyone know of a workaround for this?
Solved! Go to Solution.
I’ve got response from the Product Team.
This is outside the control of Power BI. We rely on the MySQL ADO.NET driver to correctly execute this code. It appears that in this case, it does not work.
Best Regards,
Herbert
Yes! Workaround is to change @csum to @'csum'
This fixed my issue immediately!
Great, that's it!
I’ve got response from the Product Team.
This is outside the control of Power BI. We rely on the MySQL ADO.NET driver to correctly execute this code. It appears that in this case, it does not work.
Best Regards,
Herbert
There is a solution: check post below
I'd imagine you can write query without using MySQL variable, using correlated query for cumulative sum column.
See link for some sample (Tom H's post).
https://stackoverflow.com/questions/439138/running-total-by-grouped-records-in-table
Another option may be to do cumulative sum calculation in PowerBI and not at query stage.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |