Forum Discussion

TusharGaurav's avatar
TusharGaurav
Icon for Helper III rankHelper III
2 years ago

How to show data by creating calculation combining two tables?

Hi Experts,

 

There are two tables in my report i.e.

Position Table:

OrderIdSubOrderIdPositionNumber
12345671position1
12345672position2
12345672position3

 

Order_Parent Table:

OrderIdSubOrderIdProductNOProductPartNOManufacturerCommodityNO
12345671777777PP1MSFT_INC123
12345672888888PP2MSFT_INC321
12345673999999PP3MSFT_INC456

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:

OrderIdSubOrderIdProductNOProductPartNOManufacturerCommodityNOPositionNumber
12345671777777PP1MSFT_INC123position1
12345672888888PP2MSFT_INC321position2
12345672999999PP3MSFT_INC456position3

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

Need Help

2 Replies

  • Sergii24's avatar
    Sergii24
    Icon for Super User rankSuper User
    2 years ago

    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?

  • TusharGaurav's avatar
    TusharGaurav
    Icon for Helper III rankHelper III
    2 years ago

    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.