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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
weberna
Frequent Visitor

Linking two tables for a measure

Hello all!
I have the following problem:
I have a table that contains, among other things, a column with item numbers, a column with the stock of the items, a column with the numbers of the products in which each item is included and a column with the number of items that are included in each product.

Screenshot_20220702-210426_Office.jpg


Now there is a second table that contains the number of products to be manufactured for the next 12 months. One column contains the product numbers and the other 12 columns, each of which represents a month, contain the order numbers for the corresponding product.

Screenshot_20220702-210801_Office.jpg



My goal is now to carry out a comparison and to have a value output as the result. Specifically, I would like to know up to which month production can be carried out with the current stock. To do this, I first need to determine from the first table how many products can be manufactured with the stock, i.e. the stock divided by the number of articles per corresponding product. Then the order numbers are to be subtracted from this number of producible products from the second table on a monthly basis until the result is 0. The link between the two tables must be the product numbers. The months up to the last month that can still be "served" with the stock are to be added up in order to be able to say how long production can continue according to the order situation. If it is >=12 months, I want the value 1 as the result. If it is >=6 months but <12 months, the result should be 2. And with <6 months the result is 3.
I hope it is understandable what I intend to do and anyone can give me a saving tip. Thank you very much for your help! 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @weberna ,

 

Please follow these steps:

1. Create relationship between the two tables:

vjianbolimsft_0-1657087512535.png

2. Create a measure to calculate the result:

 

Measure =

VAR _a =

    DIVIDE (

        MAX ( 'Table1'[stock] ),

        MAX ( 'Table1'[number of items per product] )

    )

VAR _s1 =

    MAX ( 'Table2'[M01] ) + MAX ( 'Table2'[M02] )

        + MAX ( 'Table2'[M03] )

        + MAX ( 'Table2'[M04] )

        + MAX ( 'Table2'[M05] )

        + MAX ( 'Table2'[M06] )

VAR _s2 =

    MAX ( 'Table2'[M07] ) + MAX ( 'Table2'[M08] )

        + MAX ( 'Table2'[M09] )

        + MAX ( 'Table2'[M10] )

        + MAX ( 'Table2'[M11] )

        + MAX ( 'Table2'[M12] )

RETURN

IF ( _a <= _s1, 3, IF ( _a >= ( _s1 + _s2 ), 1, 2 ) )

 

Final output:

vjianbolimsft_1-1657087512540.png

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jianboli-msft
Community Support
Community Support

Hi @weberna ,

 

Please follow these steps:

1. Create relationship between the two tables:

vjianbolimsft_0-1657087512535.png

2. Create a measure to calculate the result:

 

Measure =

VAR _a =

    DIVIDE (

        MAX ( 'Table1'[stock] ),

        MAX ( 'Table1'[number of items per product] )

    )

VAR _s1 =

    MAX ( 'Table2'[M01] ) + MAX ( 'Table2'[M02] )

        + MAX ( 'Table2'[M03] )

        + MAX ( 'Table2'[M04] )

        + MAX ( 'Table2'[M05] )

        + MAX ( 'Table2'[M06] )

VAR _s2 =

    MAX ( 'Table2'[M07] ) + MAX ( 'Table2'[M08] )

        + MAX ( 'Table2'[M09] )

        + MAX ( 'Table2'[M10] )

        + MAX ( 'Table2'[M11] )

        + MAX ( 'Table2'[M12] )

RETURN

IF ( _a <= _s1, 3, IF ( _a >= ( _s1 + _s2 ), 1, 2 ) )

 

Final output:

vjianbolimsft_1-1657087512540.png

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@weberna , You should unpivot the second table. If possible convert month no to months.

 

Create a common Item/ Product and date table and join both tables together

 

https://radacad.com/pivot-and-unpivot-with-power-bi

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.