Forum Discussion
SUMX issues - converting from Import to DirectQuery
Hi Anonymous
I could not reproduce this. I import your data into a SQL Server database and connect to it in Direct Query mode. When I use this measure in a matrix visual, it works well.
What data types are your units and unitsell columns in the data source? Do you perform any transformation steps in Power Query Editor?
Regards,
Community Support Team _ Jing
Hi amitchandak - thanks for offering to help - much appreciated.
Firstly I have a projectorders table which looks something like this - each one associated with a project ID
| Project ID | ProjectOrders | Units | UnitSell |
| exampleprojectID1 | exampleprojectorderID1 | 2 | £500 |
| exampleprojectID1 | exampleprojectorderID2 | 1 | £500 |
| exampleprojectID2 | exampleprojectorderID3 | 5 | £500 |
| exampleprojectID2 | exampleprojectorderID4 | 2 | £300 |
I'm trying to create a Matrix visual that then contains a list of all the projects but roll up the project budget (units * unit sell) from the project orders associated with the individual projects. The bit is red is where I'm struggling! I've tried using SUMX but it fails when added to a matrix visual in DirectQuery mode (works with import mode).
| Project ID | Project Description | Project Manager | Project budget |
| exampleprojectID1 | egProjectDescription1 | egPMName1 | £1500 (sum of related ProjectOrders in above table) |
| exampleprojectID2 | egProjectDescription2 | egPMName2 | £3100 |
I hope that helps explain what I'm trying to acheive!
Sorry - those tables didnt format well once submitted. I'll try again here:
Table:
Matrix visual content: