Forum Discussion
Writing a measure between 4 different tables
I am relatively new to Power BI and the DAX language. Here is my issue:
Table (relevant columns to form relationships):
Cube (BoxSize (Varchar), Box Size Numeric (Decimal)
Inventory (BoxSize (Varchar), Inventory Identifier)
Sales Order (Sales Order Number)
Sales Order Transaction (Sales Order Number, Inventory Identifier, Shipped Qty)
I want to know the numeric size of every sales order, in either the Sales Order Table and/or the Sales Order Transaction tables. So essentially I am looking to write a measure that is:
'Cube'[box size numeric] * 'Sales Order Transaction'[Shipped Qty] for each individual Sales Order
Does anyone have advice for how to do this? I have added the 'Cube'[Box Size Numeric (Decimal] to the Sales Order Transaction table using the LOOKUP function, but I'm not sure that this helps.
sorry about before. I had to run and didn't want to give you another wrong answer, so I needed to test. This should work
=sumx(size,CALCULATE(sum(Orders[Qty])) * size[Size])
edit: Sorry, this next one is better and what I had in mind originally
=sumx(Orders,Orders[Qty] * RELATED(size[Size]))
Just rename the columns you need
Best to avoid calc columns if you can. It's not wrong, just better http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
6 Replies
- MattAllington
Community Champion
Read my article about data shape here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
you should reshape your tables before loading. It looks like a Sales Order is superfluous, so don't load it. I can't tell if you should join Cube and inventory into a single table, but it looks like maybe. You need a single lookup (dimension) table that contains all the unique inventory items and their cube. Join this (1:many) to the Sales Order Trans table and rename it to make it easier.
Then you can write this measure
total cube = sumx(values(transactions[order number]),transaction[qty] * related(cube[size]))
Hi Matt,
Thank you for your prompt reply.
I have done as you suggested and created a separate lookup table that contains the Inventory # and their cube.
However, the formula you provided is not working. It does not allow me to use transaction[qty] * related(cube[size]) and I'm not really sure what the issue is. The error message that pops up under the DAX script says "A single value for column 'Order Qty (ncustord)' in table 'Sales Order Transaction' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max..."
- MattAllington
ahhh, my bad. Sorry I am about to run into a meeting. I will come back to this
