Forum Discussion
SUMX issues - converting from Import to DirectQuery
Hi - I'm hoping someone can help. I'm switching from an Import report to a DirectQuery and recreating the measures I created for the Import tables. One of the measures I have that worked using Import mode is as follows:
ProjectBudget = SUMX('projectorders','projectorders'[units]*'projectorders'[unitsell])This is multiplying the number of units by the sell price of the unit to show the total project budget. The measure works (ie. no errors on the measure itself) however when displayed in a Matrix, it can't display citing an ODBC error and suggesting using a simpler expression. Trouble is, I'm not knowledgeable enough in Power BI to know what that might look like! It seems SUMX perhaps isnt supported in DirectQuery mode but I'm unsure how to acheive the same as I used for Import mode.
Any help will be very gratefully received! Thanks.
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
6 Replies
Anonymous , I think this may be because of line-level calculation which returns more than 1 Million rows.
What are expected lines at row level
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!
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 _ JingHi v-jingzhang , thanks for trying this out. The 'Units' and 'UnitSell' are decimal and currency data types respectively. The source is from a table in Dataverse. The only transformation in power query editor is to filter some rows and rearrange columns.
Many thanks.
