Relateadtable used in calculate function at calculate column based on inactive relationship

[taimaizumi]

Hi , team

I'm at lost when using relatedtable in calculate function at calculate column.

The detail is as follows.

<Data>
Sales

-->Sales can oocur at different location with same product

Product

-->Product has different location for sales

<Model>

-->Product and sales is related with Key1(on product side) and Key(on sales side) by default
and Key2(on product side) is used as inactive relationship with Key(on sales side)

<Requirement>
Compute max sales quantity for each location

<My solution>
To compute max sales for location 1, I wrote below code using default relationship

MaxQuantityKey1 = calculate(maxx('Sales','Sales'[Quantity]))

And to compute max sales for location 2, I wrote below code using Userelationship so that I can use inactive relationship

MaxQuantityKey2 = CALCULATE(maxx(relatedtable('Sales'),'Sales'[Quantity]),USERELATIONSHIP('Product'[Key2],Sales[Key]))

<My question>
Where is filter context generated by row context based on active relationship?
-->
I think row context is transformed into filter context because calculate is invoked and this filter context is based on active relationship. And then new filter context is generated by relatedtable based on inactive relationship.
Finally I think these two filter context don't have intersect resulting in blank.
But result is like this , but relationship 

[taimaizumi]

@v-yiruan-msft 
Thank you for your support.

I understand your solution and it will help me!

[v-yiruan-msft · Accepted solution]

Hi @taimaizumi ,

For the table "Product", you can do some processing in Power Query Editor as shown below, so that it will be easier to make some calculation later.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMDBU0lECYSMgBvN1wUJQppFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Location1 = _t, Location2 = _t, Key1 = _t, Key2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Location1", Int64.Type}, {"Location2", Int64.Type}, {"Key1", type text}, {"Key2", type text}}),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Changed Type", {"Location2", "Location1"}, "Attribute.1", "Location"),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Unpivoted Only Selected Columns1", {"Key1", "Key2"}, "Attribute", "Key"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute.1", "Attribute"})
in
    #"Removed Columns"

If the above one can't help you, could you please provide more raw data and the specific examples to explain your expected result( Is it 400)? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

Best Regards