How to show data by creating calculation combining two tables?

[TusharGaurav]

Hi Experts,

There are two tables in my report i.e.

Position Table:

OrderId	SubOrderId	PositionNumber
1234567	1	position1
1234567	2	position2
1234567	2	position3

Order_Parent Table:

OrderId	SubOrderId	ProductNO	ProductPartNO	Manufacturer	CommodityNO
1234567	1	777777	PP1	MSFT_INC	123
1234567	2	888888	PP2	MSFT_INC	321
1234567	3	999999	PP3	MSFT_INC	456

I need to create a table visualization where I need to bring all columns from Order_Parent table and "PositionNumber" column from Postion table.

The column OrderId and SubOrderId are common to both of the tables.

If you observe, first table have SubOrderId as 1/2 whereas second table have SubOrderId 1/2/3.

So I need to show the below records in my table Visualization:

OrderId	SubOrderId	ProductNO	ProductPartNO	Manufacturer	CommodityNO	PositionNumber
1234567	1	777777	PP1	MSFT_INC	123	position1
1234567	2	888888	PP2	MSFT_INC	321	position2
1234567	2	999999	PP3	MSFT_INC	456	position3

When I am trying to bring PostionNumber by joining the two tables in modelling layer in power bi desktop,I am getting 9 rows as there is n:n caridinality.

I cant create join by combining OrderId and SubOrderId,as there are millions or records and power bi is giving memory issue, while showing the table visualization.

I there tried the below calculation:

PostionNumber_measure= calculate(firstnonblank('Position'[PostionNumber],1),

                                            filter('PositionNumber',

                                                      'Position'[OrderId] = Max('Order_Parent'[OrderId])))

But I am not getting the correct result.

Can you please help me in getting the correct result.

Thanks and Regards,

Tushar

[TusharGaurav]

Hi Sergii,

Thanks a lot for your response.

Actually we need to create relationship on "Order_ID".

In one order_id there can be multiple suborder_ids, however it might be possible that few "suborder_ids" present in "Order_parent" table may not present in "Position"table.

However we need to show the "positionNumber" of all the "suborder_ids" present in "Position"table.

I am planning to use the below calculation:

PostionNumber_measure= calculate(firstnonblank('Position'[PostionNumber],1),

filter('PositionNumber',

'Position'[OrderId]&'Position'[SubOrderId] = Max('Order_Parent'[OrderId])&Max('Order_Parent'[SubOrderId])))

But ,I think it will impact the performance and looking for better approach.

[Sergii24]

Hi @TusharGaurav, can you share with us a screenshot of your semantci model from model view tab? What is a key between tables Order_Parent and Position? What is a role of SubOrder ID? Can one Order ID have multiple SubOrder IDs?