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

     

     

    • jdbuchanan71's avatar
      jdbuchanan71
      Icon for Super User rankSuper User
      7 years ago

      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.