Lookup base from Two Column

[AllanBerces]

Hi Good day, 

Pls need help on Lookupvalue or any other way to have the result i need. I have two table table 1 Require and Table 2 avialble. I want to have ACTUAL AVAI. and PENDING Column base from Task and Article Column. For Pending If available is greaterthan required = 0 instead of negative value.

Thank you

[Anonymous · Accepted solution]

Hi @AllanBerces,

You can try to use the following calculated table expression to create a new table to show expected results if it suitable for your requirement:

NewTable =
ADDCOLUMNS (
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [Task],
        [Article],
        [Required],
        "Available",
            CALCULATE (
                SUM ( Table2[Available] ) + 0,
                FILTER (
                    ALLSELECTED ( Table2 ),
                    Table2[Task] = [Task]
                        && Table2[Article] = [Article]
                )
            )
    ),
    "Pending",
        IF ( [Required] - [Available] > 0, [Required] - [Available], 0 )
)

Regards,

Xiaoxin Sheng