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

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.