Calculated table with summarizing of another table in a many to many relationship

[Applicable88]

Hello,

recently someone taught me that for summarizing columns from two different tables there is no need for related() function. 

But when I try to add addtional filter in it the syntax is greyed out. I have a Fact table and a materialtable. Both are connected via Material columns. They exist in a many to many relationship. 

Facttable:

Order	Date	Type	Status	Category	Material
12345	01.01.2021	5LL	DLVR	Manu	ZZZ
12346	02.01.2021	8ZL	DLVR	Manu	ZZZ
12347	03.01.2021	888	PRCC	Manu	ZZZ
12348	04.01.2021	888	PRCC	Plan	ZZZ
12349	05.01.2021	888	PRCC	Plan	ZZZ
12350	06.01.2021	888	PRCC	Manu	BB
12351	07.01.2021	888	PRCC	Manu	BB

Materialtable:

Material	TotalAmount	Date	Reserve
ZZZ	2000	06.07.2021	11
ZZZ	2000	06.07.2021	58
ZZZ	2000	06.07.2021	33
ZZZ	2000	06.07.2021	77
ZZZ	2000	07.07.2021	55
ZZZ	2000	08.07.2021	44
BB	2000	09.07.2021	33
BB	2000	10.07.2021	55

I first tried to just grouping the materialtable to one date and add sum of the reserve as an extra column:

Summarizedtable = ADDCOLUMNS(SUMMARIZE(Material, Material[Date],Material[TotalAmount],Material[Material]),"SumRes", calculate(sum(Material[Reserve])))

So now I have all materials grouped to one date. In the next step I actually tried to filter my new calculated table to following conditions which are based on another table, the fact table: Only show me the materials of orders where  Type = 888 or 8ZL, Status <> "DLVR", Category = "Plan"

So I added the conditions like these into the summarize part of the Summarizedtable:

'FACT'[Type] in {"888","8ZL"} ,NOT(CONTAINSSTRING('FACT'[Status],"DLVR")),'FACT'[CATEGORY] = "PLAN"))

But its all greyed out. 

How can I group the table and at the same time filter it?

Thank you very much in advance.

Best. 

[Applicable88]

@amitchandak thanks.

The formula works, but if I connect the new table to the material table I get "circle dependencies" , what can I do to avoid this?

[v-rzhou-msft · Accepted solution]

Hi @Applicable88 

Your issue is circular dependencies in relationships. This issue is caused by you build the relationship from new table to Material Table. Your new table is a calcualted table by Dax depends on Materials table, so you builld relationship from it to Material Table may cause circular dependencies error. You can try to build the relationship from Material Table to new table. I have a test by your sample and it works.

Build relationship from new table to Material Table, we will get issue.

Build relationship from Material Table to new table, it works.

Here is a blog for circular dependencies, you may refer to it for more details.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Applicable88 , You should be add those condition in new column you want to add from fact

'FACT'[Type] in {"888","8ZL"} ,NOT(CONTAINSSTRING('FACT'[Status],"DLVR")),'FACT'[CATEGORY] = "PLAN"))

or try calculatetable like

ADDCOLUMNS(SUMMARIZE(calculatetable(Material,

'FACT'[Type] in {"888","8ZL"} ,NOT(CONTAINSSTRING('FACT'[Status],"DLVR")),'FACT'[CATEGORY] = "PLAN"), Material[Date],Material[TotalAmount],Material[Material]),"SumRes", calculate(sum(Material[Reserve])))