Forum Discussion
New summarized table from multiple tables with all unique rows
Hi All,
I'm new here and happy to join, you helped me a lot in the past with great posts.
Now I have a task and I could'nt find the right sollution.
I have 2 tables.
- First has the sales data
- 2nd has special kinf ofCosts.
Sales table is more detailed, it contains the Product ID as well, but in the new Table I don't need that.
As you can see in the picture below, I need to create a new table with all unique rows, with sum of sales and costs. If the row has only one data from these 2 i need that as well.
I want to do it with DAX.
Thank you
Hi,
Thank you. I made it like these. and it working in visualizations. It is hard to explain why i need it now, but you know, special requests 🙂
but I made it, not sure this is the most simple way, but working :
First I made 2 summarized table tfromData and tFromCostUnion_table =
var _t =DISTINCT(
UNION(
SELECTCOLUMNS(tFromData,"Q",tFromData[YearQuarter],"cs2",tFromData[Category],"v",tFromData[Customer]),
SELECTCOLUMNS(tFromCost,"Q",tFromCost[YearQuarter],"cs2",tFromCost[Category],"v",tFromCost[Customer])
)
)
return
ADDCOLUMNS(_t,
"Sales",LOOKUPVALUE(tFromData[Sales],tFromData[YearQuarter],[Q],tFromData[Category],[cs2],tFromData[Customer],[v]),
"Cost",LOOKUPVALUE(tFromCost[Cost],tFromCost[YearQuarter],[Q],tFromCost[Category],[cs2],tFromCost[Customer],[v])
)
4 Replies
- amitchandak
Super User
ugux , In power query, append the tables and delete duplicate rows
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
In Dax
new table = distinct(union(Table1, Table2))
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Thank you Amit, this DAX is working, but it is not as simple because as I mentioned I need 2 Data columns in the new table. Sales and Costs, and Sales table have more columns than Costs.
I tried to lookup the costs into Sales table but in this way I loose some Cost data, because I have cost rows which do not have Sales.
Thank you
PaulDBrownCommunity Champion
I recommend you set up your model with dimension tables containing unique values for each corresponding fields, add a calendar table containing conitnuous dates covering the whole range of dates in the model, and join these tables with the corresponding fields in each fact table. You then build the visuals using the fields from the dimension tables.
Hi,
Thank you. I made it like these. and it working in visualizations. It is hard to explain why i need it now, but you know, special requests 🙂
but I made it, not sure this is the most simple way, but working :
First I made 2 summarized table tfromData and tFromCostUnion_table =
var _t =DISTINCT(
UNION(
SELECTCOLUMNS(tFromData,"Q",tFromData[YearQuarter],"cs2",tFromData[Category],"v",tFromData[Customer]),
SELECTCOLUMNS(tFromCost,"Q",tFromCost[YearQuarter],"cs2",tFromCost[Category],"v",tFromCost[Customer])
)
)
return
ADDCOLUMNS(_t,
"Sales",LOOKUPVALUE(tFromData[Sales],tFromData[YearQuarter],[Q],tFromData[Category],[cs2],tFromData[Customer],[v]),
"Cost",LOOKUPVALUE(tFromCost[Cost],tFromCost[YearQuarter],[Q],tFromCost[Category],[cs2],tFromCost[Customer],[v])
)
