Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
taimaizumi
Helper I
Helper I

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

Hi , team

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

The detail is as follows.

<Data>
Sales

taimaizumi_0-1682318076371.png

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

Product

taimaizumi_1-1682318096111.png

-->Product has different location for sales


<Model>

taimaizumi_2-1682318175723.png

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

taimaizumi_3-1682318331886.png


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

taimaizumi_4-1682318446041.png

 

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

 

taimaizumi_5-1682318535840.png

<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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

vyiruanmsft_0-1682488062182.png

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

View solution in original post

2 REPLIES 2
taimaizumi
Helper I
Helper I

@Anonymous 
Thank you for your support.

I understand your solution and it will help me!

Anonymous
Not applicable

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"

vyiruanmsft_0-1682488062182.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.