Forum Discussion

CalClack's avatar
CalClack
Helper II
6 years ago
Solved

Substitute Value using If Then Else

I would like to a calculated column value from one table if condition is true, else use a column value from another table if false. For instance in the screen shot if Ithere is a value > 0.00, I'd like to use the value,  from one table, G Calculated Electricity % dimPeriod into the column "NewElec%", otherwise use the value, G Calculated Electric % factMeterReading from a second table.  My statement for the calculated column is:

 

NewElec% = IF(max('dim_Period'[G Calculated Electricity % dimPeriod]) > 0.00,
(max('dim_Period'[G Calculated Electricity % dimPeriod])),
(max('fact_MeterReading'[G Calculated Electric % factMeterReading]))
)

 

 

 
  • Anonymous's avatar
    Anonymous
    6 years ago
    Hi. First, there's no screenshot. Second, a calculated column like this should be created and calculated during the ETL phase, possibly in Power Query. Calculated columns are not compressed optimally when created with DAX.

    Best
    D

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi CalClack 

    try smth like

    NewElec% = 
var _maxDimPeriod = CALCULATE(max('dim_Period'[G Calculated Electricity % dimPeriod]))

RETURN
IF(_maxDimPeriod  > 0,
_maxDimPeriod ,
max('fact_MeterReading'[G Calculated Electric % factMeterReading])
)
    • CalClack's avatar
      CalClack
      Helper II
      6 years ago

      I'll give it a shot.  Thanks for the rapid reply

    • CalClack's avatar
      CalClack
      Helper II
      6 years ago

      AZ38,

       

      I'd like to pursue your proposed solution, but I'm not understanding the format you have propsed.  I should have probably attacked it from the backend at the database level, but if you technique works, then that would be another feather in the hat.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hi. First, there's no screenshot. Second, a calculated column like this should be created and calculated during the ETL phase, possibly in Power Query. Calculated columns are not compressed optimally when created with DAX.

    Best
    D
    • CalClack's avatar
      CalClack
      Helper II
      6 years ago

      Thanks, I will take a look at the ETL phase of calculating.