Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Measure with columns from different tables

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 - retriev...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      7 years ago

      Hello Anonymous ,

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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