Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to reference ifs in DAX

I need to say

 

 

AVB = IF('SageFunctCodes$'[type]="Cost",Budgets[Budget]-Transactions[Actual],IF('SageFunctCodes$'[Type]="Revenue",Transactions[Actual]-Budgets[Budget],0))

 

but in a measure.  When I try to copy the code in the measure it does not recognise the SageFunctCodes columns.  How would I go about replicating the above in a Dax measure.

 

Thanks

 

C

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Found 

     

    AvB = IF(COUNTROWS(VALUES( 'SageFunctCodes$'[Type] ))=1, 
        IF(VALUES( 'SageFunctCodes$'[Type] ) =  "Cost", 
         Budgets[Budget]-Transactions[Actual], 
         IF(VALUES( 'SageFunctCodes$'[Type] ) =  "Revenue", 
         Transactions[Actual]-Budgets[Budget]) ),Transactions[Actual]-Budgets[Budget])

    This works.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Found 

     

    AvB = IF(COUNTROWS(VALUES( 'SageFunctCodes$'[Type] ))=1, 
        IF(VALUES( 'SageFunctCodes$'[Type] ) =  "Cost", 
         Budgets[Budget]-Transactions[Actual], 
         IF(VALUES( 'SageFunctCodes$'[Type] ) =  "Revenue", 
         Transactions[Actual]-Budgets[Budget]) ),Transactions[Actual]-Budgets[Budget])

    This works.