Forum Discussion

Jorrafer25's avatar
Jorrafer25
Frequent Visitor
3 years ago
Solved

Filter a table based other filtered table

Hello community! I need help filtering a table based on the selected values from another table. My company receives every few days the forecast about how many items should produce. That demand would...
  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    Hi Jorrafer25 ,

     

    Add a new measure to the original one

     

    Forecast =
SUMX (
    SUMMARIZE (
        FILTER (
            ALL ( 'CutOffDates' ),
            [CutOffDate] < MAX ( 'Calendar'[Date] )
                && [Version] IN VALUES ( Selection1[Version] )
        ),
        'CutOffDates'[Version],
        'CutOffDates'[PartNo],
        'CutOffDates'[CutOffDate],
        "1",
            CALCULATE (
                SUM ( 'Forecast'[Quantity] ),
                FILTER (
                    'Forecast',
                    'Forecast'[PartNo] = EARLIER ( 'CutOffDates'[PartNo] )
                        && 'Forecast'[Version]
                            IN VALUES ( Selection1[Version] )
                                && 'Forecast'[Date] > EARLIER ( 'CutOffDates'[CutOffDate] )
                )
            )
    ),
    [1]
)

     

     

    The result is as follows :

     

     

    Best Regards,

    Gallen Luo

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

v-jialluo-msft's avatar
v-jialluo-msft
Community Support
3 years ago

Hi Jorrafer25 ,

 

Please follow these steps:

(1) Edit table relationships

 

(2) Add a new measure

Export =

SUMX (

    SUMMARIZE (

        FILTER (

            ALL ( 'CutOffDates' ),

            [CutOffDate] > MAX ( 'Calendar'[Date] )

                && [Version] IN VALUES ( Selection1[Version] )

        ),

        [Version],

        [PartNo],

        [CutOffDate],

        "1",

            CALCULATE (

                SUM ( 'Exported'[Quantity] ),

                FILTER (

                    'Exported',

                    [PartNo] = EARLIER ( 'CutOffDates'[PartNo] )

                        && [Date] <= EARLIER ( 'CutOffDates'[CutOffDate] )

                )

            )

    ),

    [1]

)

 

(3) The result is as follows :

 

 

 

 

Best Regards,

Gallen Luo

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

  • Jorrafer25's avatar
    Jorrafer25
    Frequent Visitor
    3 years ago

    Hello Gallen Luo!

    Thanks so much for the answer! I think it's really really close for what I need. Unfortunatelly, I think It's failing in the measure ForecastQtySelection1. This measure should contains only the quantities from the cut off dates onwards. 

     

    What I need is:

     

    Thanks so much!

    Best Regards