Forum Discussion

Yura_greenit's avatar
Yura_greenit
Frequent Visitor
6 years ago
Solved

Many-to-many connection between tables

Hello everyone.

I have faced with the following problem::

I have two main tables:
Cash Flow table and Land Bank table

The Cash Flow table shows the expenses on fields by date.

The Land Bank table shows the movement of the land bank (change of field area by date).

The common columns between the tables "Field" and "Actual Crops", but the Land Bank table also has a "Planned Crops" column.

It should be shown Expenses on the date of the planned crops.

I wrote a measure that calculates the expenses per date by the Cash Flow table, and I also count the area of each field on the date by the Land Bank table. There is Many-to-many connection between tables.

How to display expenses in the context of planned crops in one visualization? I would appreciate any help.

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Yura_greenit,

     

    Would you please try to create a measure for value:

    Value2 =

DIVIDE (

    SUM ( 'Land Bank'[Field area] ),

    CALCULATE (

        SUM ( 'Land Bank'[Field area] ),

        FILTER (

            ALL ( 'Land Bank' ),

            'Land Bank'[Actual Crops] IN DISTINCT ( 'Cash Flow'[Actual Crops] )

        )

    )

)

    * SUM ( 'Cash Flow'[Value] )

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

     

    Best Regards,

    Dedmon Dai

4 Replies