SUMX issues - converting from Import to DirectQuery

[Anonymous]

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.

[v-jingzhang · Accepted solution]

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

[Anonymous]

After creating a new dataset with the data and not applying any 'type' as 'decimal' to the columns I managed to get this working. Thanks for sowing the seed. I just needed to ensure the columns had the right type set. Thanks so much!

[Anonymous]

Hi @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.

[amitchandak]

@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

[Anonymous]

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!

[Anonymous]

Sorry - those tables didnt format well once submitted. I'll try again here:

Table:

Matrix visual content: