Forum Discussion

Quinn_921's avatar
Quinn_921
New Member
8 years ago

DAX IF Statement not calculating correctly

I have a table with a number of measures - one of them as displayed below checks Project Labor budgets and if they are blank assigns a "0" value, if they're non blank the expression calculates.

 

Labor Invoiced + Billable Non Invocied + Billable Fixed Fee = IF([Project Labor Budget]=0,BLANK(),InvoiceTransactions[Transaction Gross Amount]+InvoiceTransactions[Transaction Write-Off Amount]
+CALCULATE(SUM(Transactions[FixedFeeRevenue]),Transactions[Is Transaction Invoiced]="Not Invoiced",Transactions[Is Transaction Billable]="Billable")
+CALCULATE(SUM(Transactions[LaborRevenue]),Transactions[Is Transaction Invoiced]="Not Invoiced",Transactions[Is Transaction Billable]="Billable"))

I use this measure to derive the total backlog remaining in my umage below - However in my table the total values are calulating as if I hadn't used that IF Statement.

 

Table Values

 

Can anyone shed some light on what i'm doing wrong here?

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Quinn_921

     

    Is this a MEASURE?

     

    Could you try this

     

    Labor Invoiced + Billable Non Invocied + Billable Fixed Fee =
IF (
    SELECTEDVALUE ( [Project Labor Budget] ) = 0,
    BLANK (),
    SELECTEDVALUE ( InvoiceTransactions[Transaction Gross Amount] )
        + SELECTEDVALUE ( InvoiceTransactions[Transaction Write-Off Amount] )
        + CALCULATE (
            SUM ( Transactions[FixedFeeRevenue] ),
            Transactions[Is Transaction Invoiced] = "Not Invoiced",
            Transactions[Is Transaction Billable] = "Billable"
        )
        + CALCULATE (
            SUM ( Transactions[LaborRevenue] ),
            Transactions[Is Transaction Invoiced] = "Not Invoiced",
            Transactions[Is Transaction Billable] = "Billable"
        )
)

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Quinn_921,

     

    Can you please share some sample to test? It will be help to clarify your situation.

    In addition, you can also try to use below formula if it works on your side.

    Calculate column formula:

     

    Labor Invoiced + Billable Non Invocied + Billable Fixed Fee =
IF (
    [Project Labor Budget] = 0,
    BLANK (),
    InvoiceTransactions[Transaction Gross Amount]
        + InvoiceTransactions[Transaction Write-Off Amount]
        + SUMX (
            FILTER (
                ALL ( Transactions ),
                Transactions[Is Transaction Invoiced] = "Not Invoiced"
                    && Transactions[Is Transaction Billable] = "Billable"
            ),
            [FixedFeeRevenue] + [LaborRevenue]
        )
)

     

     

    BTW, 'Project Labor Budget' is a measure who host on other table, right?

     

    Regards,

    Xiaoxin Sheng