Summarize table depending on several fact tables

[Markando]

I have built a table containg all active teams and their corresponding sales, shipments and cases which works fine. The goal is to create a table with only active teams which I can then use as a slicer and make it a bit more user friendly

However it would be even better if I didn't have to make my calculations (SUMx3), but I can't get it working when the team code is in three different fact tables. Do anyone know how I can recreate  this table with only all distinct Team[Code] that is available in the three fact tables?

My current DAX-code:

SUMMARIZECOLUMNS(

Team[Code];Team[Name];
"SUM OF SALES";calculate(sum(Sales[SalesAmount]));
"SUM OF CASES";calculate(countrows('Cases'));
"SUM OF SHIPMENTS";calculate(sum(Shipment[Shipments]))

)

[vanessafvg]

@Markando you could create one table with the combined code values create a relationship between all the tables and the new lookup table and then summarize?

be best do it in power query / query editor.

duplicate all the tables, remove everythign except the code column, append them together and then deduplicate, add it to your model and create a relationship.  not sure if that will do it but it might

[Markando]

Hi Vanessafvg, thanks for your reply. 

The powerquery works fine, however I would prefer to do it in dax. I know how I put in a table in the summarizefunction, however for this case i need to build a new tablefunction containg the distinct team code value from three different fact-tables, its there I'm currently stuck. 

[vanessafvg]

@Markando where are you summarising at the moment, in a new table?  or not ? sorry it would be best if i could see what you were actually doing? and the error or result you getting

[Markando]

Hi

Im creating a new table. Let me try to show with a detailed summary:

I got five tables as below:

TEAM
Code	Name
1	A
2	B
3	C
4	D
CUSTOMERS
CustomerCode	TeamCode
1	1
2	2
3	3
4	4
Sales
CustomerCode	Amount
1	1000
Shipments
CustomerCode	Shipments
2	400
Cases
CustomerCode	Shipments
3	5000

What I want to do is create a new table with all teams that has been active in either of the Sales, shipments or cases table. The primary key in these tables are customerCode. All CustomerCode is related to a single teamcode. 

My DAX above works perfect and I get this table:

TeamCode	TeamName	SUM OF SALES	SUM OF CASES	SUM OF SHIPMENTS
1	A	1000
2	B			400
3	C		5000

However I would like the same table, but withouth the summing. If I remove the sums I get a summarize over the entire team-table and thus also showing team D.

[v-xjiin-msft]

Hi @Markando,

Why don't you want to use SUMMARIZE()? 

Per my understanding about your issue, the SUMMARIZE() should be the easiest way to achieve your requirement since you don't want to use Power Query.

Thanks,
Xi Jin.

[Markando]

Hi

I'm more then happy to use summarize()! However my question is how do i use a filter in either summarize or summarizecolumns to get a list as below depending on all the team-values that exist in either of the three fact tables