Substitute Value using If Then Else

[CalClack]

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 · Accepted solution]

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]

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

[az38]

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]

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.

[CalClack]

[CalClack]

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