Forum Discussion

andrei_iuganu's avatar
andrei_iuganu
Regular Visitor
9 years ago

power bi relationship

Hi all. I'm totally a beginner ... and I need a lot of help from you. I have an excel file. With the help of Power BI I would like to get the following thing: The "startstock" worksheet compares i...
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
9 years ago

andrei_iuganu,

 

You may use DAX below to add a calculated table.

Table =
ADDCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            startstock,
            "end stock", LOOKUPVALUE ( endstock[stock], endstock[code_item], startstock[code_item] )
        ),
        NOT ( ISBLANK ( [end stock] ) )
    ),
    "quantity consumed", startstock[stock] - [end stock]
)
andrei_iuganu's avatar
andrei_iuganu
Regular Visitor
9 years ago

i got this message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."..when i try your DAX formula

    • andrei_iuganu's avatar
      andrei_iuganu
      Regular Visitor
      9 years ago

      i follow the steps in tutorial and the result is this error "A table of multiple values was supplied where a single value was expected."

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        9 years ago

        andrei_iuganu,

         

        Check if the following DAX works.

        Table =
ADDCOLUMNS (
    FILTER (
        SUMMARIZE (
            startstock,
            startstock[code_item],
            "start stock", SUM ( startstock[stock] ),
            "end stock", SUMX (
                FILTER ( endstock, endstock[code_item] = startstock[code_item] ),
                endstock[stock]
            )
        ),
        NOT ( ISBLANK ( [end stock] ) )
    ),
    "quantity consumed", [start stock] - [end stock]
)