I have an inventory model that consists mainly of item master and item transactions, with supporting tables such as transaction type, aging table and transaction with max last transaction date table. This max last date table contains item number, transaction type and the last transatction date, and ranks the item in descending order, so the last transaction appears on the top of the list with a rank of 1, and so forth.
I want to report items, their last transaction date, their last transaction date for each transaction type and their age. Additionally, I want to report number of items (counts) and their age. If i drop Item numer on a table or matrix and then select Transaction Date from the max table, I get one row stating the Item Number and the Trnasaction Date. But when I drop Age from the Aging table, I get 3 rows, with same Item Number & Transaction Date but with different Age categories. Similarly, when I try to generate count of items in each age category, I am getting 3 age categories with an item ocunt of 1 for each item.
I have prepared the model (https://filetea.me/t1ss70LtgBERqmnpUpYmZL9PA) I am not sure what am I doing wrong, and now need a bit of guidance.
In Power BI, both table and matrix will aggregate fact values for each specific item automatically. However, in this scenario, since you have different Aging type associated with each items, once you drop Age into table/matrix, it will crossjoin different type of Age which returns multiple rows. So if you only want to get the data on item wise, you should't include Age into your table.
Thanks @v-sihou-msft for your reply. I understand the bit about cross join that comes into play when I include Age in the table. But the business wants to see Inventory Item, Last Transaction Date and Age at the same time. Any suggestions about how to shape the model to get this?