How not to Ignore Values Filtered by Another Table

[Natallia_23]

I have existing report and now I need to and new table without changing old tables.

Here is the peace of the model:

New Table is connected by compozite key company+contract.

Sometimes rows in New table have ContractID = 0, but ManyToMany table does not.

I need to calculate sum of Money column including rows with ContractID = 0.

Expected:

Company

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE01VIrViVYygvKMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Company Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Company Name", type text}})
in
#"Changed Type"

Contracts

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRcjZUitWJVjIyMgJxjMAcY2NjEMdYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", Int64.Type}, {"Contract Name", type text}})
in
#"Changed Type"

ManyToMany

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0NFSK1YlWMgKyjYyMwGxDNLaxsbFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Contract ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Contract ID", Int64.Type}})
in
#"Changed Type"

New

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0BJMGBkqxOhARIyMjIGmKJGJsbAwSRxIxAKtQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, #"Contract ID" = _t, Money = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Contract ID", Int64.Type}, {"Money", Int64.Type}})
in
#"Changed Type"

Additional columns:

Key = CONCATENATE(CONCATENATE(VALUE(ManyToMany[Company ID]),"_"),Value(ManyToMany[Contract ID]))

Key = CONCATENATE(CONCATENATE(VALUE(New[Company ID]),"_"),Value(New[Contract ID]))

[parry2k]

@Natallia_23 well you cannot work with incomplete data and keep the relationship you want, not sure what else to tell. Good luck!

[parry2k]

@Natallia_23 you should change the filter direction of many to many to single direction in the relationship.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Natallia_23]

Hello, I can not change old tables and relationships unfortunately.