How would I create a measure for this in PowerBI? if value of related table, return a single value

user_4459581 0
Viewed 34

I want to write a measure on the payment table where:

if(project.status = "active", sum all of payment + project.cost, sum all of payment)

The issue I have is that measure will add the cost 4 times in the sample set below if I write

measure =
SUMX (
    payment,
    IF (
        RELATED ( project[status] ) = "active",
        [amount] + RELATED ( project[cost] ),
        [amount]
    )
)

I can use either table or measure but I can't figure out a way to just pull the project cost once in a measure under the if statement.

Sample data below:

Project

ID Cost Status
1 50 Active
2 100 Inactive

Payment

Project Amount
1 50
1 100
1 200
1 300
2 50
2 100
dax powerbi
1 Answers

SUMX is an iterator. It steps through each row of the table in the first argument and adds up the result calculated for each row separately.

I think you want something more like this:

meas =
SUM ( Payment[Amount] )
    + CALCULATE (
        SUM ( Project[Cost] ),
        FILTER ( VALUES ( Project[Status] ), Project[Status] = "Active" )
    )
user_7652260
Related