Forum Discussion

Natallia_23's avatar
Natallia_23
Resolver I
4 years ago

How not to Ignore Values Filtered by Another Table

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]))
 

 

3 Replies

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

  • 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's avatar
      Natallia_23
      Resolver I

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