Measure with columns from different tables

[GY2019]

I have 3 tables:

a) sales - has multiple columns and two columns I am using for measure; name and quantity

b) cost - has multiple columns of cost for each salesperson

c) exchange rate - retrieving rate from ww.xe.com

I created a measure that will calculate the total cost for each salesperson using proper exchange rate and cost.  The calculation I have is shown below.  There is no error on the calculation but I am getting error message when I ran this:

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

My table contains thousand of lines.

Any help on the error message and correction on the test_cost is much appreciated.

thanks.

test_cost =
       SUMX (
           sales,
           sales[quantity]
              * SWITCH (
              sales[name],
               "John", Cost[cost_john],
               "Steve", Cost[cost_steve],
               "Mary", Cost[cost_mary],
               "Tiff", Cost[cost_tiff],
               "Kev", Cost[cost_kev],
               1
             )
)

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])

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

[jdbuchanan71]

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

[GY2019]

Already filtered to CAD, EUR and AUD.

let
    Source = Web.BrowserContents("http://www.xe.com/currencytables/?from=USD"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='historicalRateTbl'] > TR > :nth-child(1), TABLE[id='historicalRateTbl'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='historicalRateTbl'] > TR > :nth-child(2), TABLE[id='historicalRateTbl'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='historicalRateTbl'] > TR > :nth-child(3), TABLE[id='historicalRateTbl'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='historicalRateTbl'] > TR > :nth-child(4), TABLE[id='historicalRateTbl'] > * > TR > :nth-child(4)"}}, [RowSelector="TABLE[id='historicalRateTbl'] > TR, TABLE[id='historicalRateTbl'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"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}}),
    #"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
    #"Filtered Rows"

[GY2019]

I tried to simply the calculation to see if the error message has something to do with multiple rows to:

test_cost = 
       SUMX (
           sales,
           sales[quantity]
              * SWITCH (
              sales[name],
               "John", 1.34*Cost[john],
               1
             )
)

Still have the same exceeding error message. So problem is in the statement.

Any ideas?

[jdbuchanan71]

Hello @GY2019 ,

Would you be able to share a sample of the data model with us?  It would help to see the relationships and test.

[GY2019]

Hi @jdbuchanan71,

Unfortunately I can't share the actual data since it's query from external data source and contains millions of lines with 30 columns.  I did create a small sample file amd simplied the equation and have no issue.  But when I applied the same equation to the actual data, I ran into this exceed max allowed size of 1000000 rows.  However, if I hardcode the cost instead of cost[john_aud], I entered 45.20 for example, there is no issue.

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

)

)

[jdbuchanan71]

What if, rather than using a swith in the measure, you use dax to generate a two coumn table with the name and cost.  You can then join this to your sales table on name and your extended cost is then just.

Ext Cost = SUMX(sales,sales[Quantity] * RELATED(cost[Cost]))

It will also likely perform better.