Forum Discussion
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...
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
Super User
Super UserWhat 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.