Measure with columns from different tables

Question

I have 3 tables:&nbsp;a) sales - has multiple columns and two columns I am using for measure; name and quantityb) cost - has multiple columns of cost for each salespersonc) exchange rate - retrieving rate from ww.xe.com&nbsp;I created a measure that will calculate the total cost for each salesperson using proper exchange rate and cost.&nbsp; The calculation I have is shown below.&nbsp; There is no error on the calculation but I am getting error message when I ran this:&nbsp;The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.&nbsp;My table contains thousand of lines.&nbsp;Any help on the error message and correction on the test_cost is much appreciated.&nbsp;thanks.&nbsp;&nbsp;test_cost = &nbsp; &nbsp; &nbsp; &nbsp;SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sales[quantity]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sales[name],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"John", Cost[cost_john],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Steve", Cost[cost_steve],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Mary", Cost[cost_mary],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Tiff", Cost[cost_tiff],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Kev", Cost[cost_kev],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;))&nbsp;cost_john = Sum('Exchange Rates'[Units per USD])*Sum(Cost[john])cost_steve = Sum('Exchange Rates'[Units per CAD])*Sum(Cost[steve])cost_mary = Sum('Exchange Rates'[Units per AUD])*Sum(Cost[mary])&nbsp;namequantitytest_costMary253&nbsp;Mary127&nbsp;Steve111&nbsp;Tiff546&nbsp;John239&nbsp;Mary357&nbsp;Kev645&nbsp;Mary823&nbsp;John186&nbsp;John922&nbsp;Steve748&nbsp;&nbsp;

jdbuchanan71 · Answer

It reads like you are pulling back to many rows from&nbsp;ww.xe.com.&nbsp; If you just ask for all exchnage rates for all pairs for all time you will get a pretty big data set.&nbsp; Perhaps apply some filters to your query to a time range and from and to countries?

anonymous · Answer

Already filtered to CAD, EUR and AUD.&nbsp;let&nbsp;&nbsp;&nbsp; Source = Web.BrowserContents("http://www.xe.com/currencytables/?from=USD"),&nbsp;&nbsp;&nbsp; #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='historicalRateTbl'] &gt; TR &gt; :nth-child(1), TABLE[id='historicalRateTbl'] &gt; * &gt; TR &gt; :nth-child(1)"}, {"Column2", "TABLE[id='historicalRateTbl'] &gt; TR &gt; :nth-child(2), TABLE[id='historicalRateTbl'] &gt; * &gt; TR &gt; :nth-child(2)"}, {"Column3", "TABLE[id='historicalRateTbl'] &gt; TR &gt; :nth-child(3), TABLE[id='historicalRateTbl'] &gt; * &gt; TR &gt; :nth-child(3)"}, {"Column4", "TABLE[id='historicalRateTbl'] &gt; TR &gt; :nth-child(4), TABLE[id='historicalRateTbl'] &gt; * &gt; TR &gt; :nth-child(4)"}}, [RowSelector="TABLE[id='historicalRateTbl'] &gt; TR, TABLE[id='historicalRateTbl'] &gt; * &gt; TR"]),&nbsp;&nbsp;&nbsp; #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),&nbsp;&nbsp;&nbsp; #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", type text}, {"Currency name#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}}),&nbsp;&nbsp;&nbsp; #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼"] = "AUD" or [#"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼"] = "CAD" or [#"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼"] = "EUR"))in&nbsp;&nbsp;&nbsp; #"Filtered Rows"

anonymous · Answer

I tried to simply the calculation to see if the error message has something to do with multiple rows to:&nbsp;test_cost =&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;sales[quantity]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sales[name],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"John", 1.34*Cost[john],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;))&nbsp;Still have the same exceeding error message. So problem is in the statement.&nbsp;Any ideas?

jdbuchanan71 · Answer

Hello&nbsp;Anonymous&nbsp;,Would you be able to share a sample of the data model with us?&nbsp; It would help to see the relationships and test.

anonymous · Answer

Hi&nbsp;jdbuchanan71,&nbsp;Unfortunately I can't share the actual data since it's query from external data source and contains millions of lines with 30 columns.&nbsp; I did create a small sample file amd simplied the equation and have no issue.&nbsp; But when I applied the same equation to the actual data, I ran into this exceed max allowed size of 1000000 rows.&nbsp; However, if I hardcode the cost instead of cost[john_aud], I entered 45.20 for example, there is no issue.&nbsp;john_aud = Sum('Exchange rate'[Units per USD])*Sum(cost[john])test_cost1 = SUMX (sales,sales[quantity]* SWITCH (sales[name],"John", cost[john_aud],"Tiff", cost[tiff_aud],1))&nbsp;&nbsp;&nbsp;

Forum Discussion

Measure with columns from different tables

6 Replies

Related content

Measure two columns

What is the difference between a measure and calculated column

Cumulative Difference from Another measure

Performance difference between calculated column or measure?

Multi X Axis with different Columns/Measures.

name	quantity	test_cost
Mary	253
Mary	127
Steve	111
Tiff	546
John	239
Mary	357
Kev	645
Mary	823
John	186
John	922
Steve	748