Variable code

[CalClack]

Community, I am baffled why my variable code is not working as programmed for.  See the attached Excel with explantion.  Basically, my first page displays just fine but the second page is exlcuding one of the fields. 

The actual code:

VariableMeasureValue = IF(MAX(dim_Column[Report_column_name])="C_electric_previous" , (SUM('fact_MeterReading'[C electric previous])),
IF(MAX(dim_Column[Report_column_name])="D_electric_ current" , (SUM('fact_MeterReading'[D electric current])),
IF(MAX(dim_Column[Report_column_name])="E_actual_calculated_units" , (SUM('fact_MeterReading'[E actual calculated units])),
IF(MAX(dim_Column[Report_column_name])="F_previous_units_used" , (SUM('fact_MeterReading'[F previous units used])),
IF(MAX(dim_Column[Report_column_name])="G_Calculated_Electric_Prct" , (SUM(fact_MeterReading[G Calculated Electric Prct])*100.00),
IF(MAX(dim_Column[Report_column_name])="I_Total_Electricty" , (SUM('fact_MeterReading'[I Billed Electricity])),
IF(MAX(dim_Column[Report_column_name])="L_Total_Gas" , (SUM('fact_MeterReading'[O Billed Gas])),
IF(MAX(dim_Column[Report_column_name])="R adjustments" , (SUM('fact_MeterReading'[R correction])),
IF(MAX(dim_Column[Report_column_name])="Total_Billing" , SUM('fact_MeterReading'[S Total Billed]))
))))))))

Thanks in advance.

[jthomson · Accepted solution]

a) Pull MAX(dim_Column[Report_column_name] as a variable at the start of your measure, would allow the if statement to look a lot cleaner. Using switch rather than if would probably be cleaner still

b) What are you trying to display? I can see from your slicer you've got two things selected, MAX(dim_Column[Report_column_name] is only going to have one value, surely?

[richbenmintz · Accepted solution]

Hi @CalClack,

Try it like this, much easier to read

VariableMeasureValue = 
IF(hasonevalue(dim_Column[Report_column_name]),
SWITCH(VALUES(dim_Column[Report_column_name]),
"C_electric_previous" , SUM('fact_MeterReading'[C electric previous]),
"D_electric_ current" , SUM('fact_MeterReading'[D electric current]),
"E_actual_calculated_units" , SUM('fact_MeterReading'[E actual calculated units]),
"F_previous_units_used" , SUM('fact_MeterReading'[F previous units used]),
"G_Calculated_Electric_Prct" , SUM(fact_MeterReading[G Calculated Electric Prct])*100.00,
"I_Total_Electricty" , SUM('fact_MeterReading'[I Billed Electricity]),
"L_Total_Gas" , SUM('fact_MeterReading'[O Billed Gas]),
"R adjustments" , SUM('fact_MeterReading'[R correction]),
"Total_Billing" , SUM('fact_MeterReading'[S Total Billed])), 
Blank())

Hope this Helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[CalClack · Accepted solution]

Yup. Much easier to read and right off I spotted the problem. 

Your code reads:

VariableMeasureValue =
IF(hasonevalue(dim_Column[Report_column_name]),
SWITCH(VALUES(dim_Column[Report_column_name]),
"C_electric_previous" , SUM('fact_MeterReading'[C electric previous]),
"D_electric_ current" , SUM('fact_MeterReading'[D electric current]),
"E_actual_calculated_units" , SUM('fact_MeterReading'[E actual calculated units]),
"F_previous_units_used" , SUM('fact_MeterReading'[F previous units used]),
"G_Calculated_Electric_Prct" , SUM(fact_MeterReading[G Calculated Electric Prct])*100.00,
"I_Total_Electricty" , SUM('fact_MeterReading'[I Billed Electricity]),
"L_Total_Gas" , SUM('fact_MeterReading'[O Billed Gas]),
"R adjustments" , SUM('fact_MeterReading'[R correction]),
"Total_Billing" , SUM('fact_MeterReading'[S Total Billed])),
Blank())

where it should be, and not fault of you because you had no idea where I was getting my column names:

VariableMeasureValue = IF(MAX(dim_Column[Report_column_name])="C_electric_previous" , (SUM('fact_MeterReading'[C electric previous])),
IF(MAX(dim_Column[Report_column_name])="D_electric_ current" , (SUM('fact_MeterReading'[D electric current])),
IF(MAX(dim_Column[Report_column_name])="E_actual_calculated_units" , (SUM('fact_MeterReading'[E actual calculated units])),
IF(MAX(dim_Column[Report_column_name])="F_previous_units_used" , (SUM('fact_MeterReading'[F previous units used])),
IF(MAX(dim_Column[Report_column_name])="G_Calculated_Electric_Prct" , (SUM(fact_MeterReading[G Calculated Electric Prct])*100.00),
IF(MAX(dim_Column[Report_column_name])="I_Total_Electricty" , (SUM('fact_MeterReading'[I Billed Electricity])),
IF(MAX(dim_Column[Report_column_name])="L_Total_Gas" , (SUM('fact_MeterReading'[O Billed Gas])),
IF(MAX(dim_Column[Report_column_name])="R_correction" , (SUM('fact_MeterReading'[R correction])),
IF(MAX(dim_Column[Report_column_name])="Total_Billing" , SUM('fact_MeterReading'[S Total Billed]))
))))))))

Thanks to all who jumped on this help right away.  You guys are the best !!!

Regards,

Cal Clack in Carmel-by-the-Sea, CA

[CalClack]

All Report_column selected but "R_correction" is the only one not being loaded.

[richbenmintz · Accepted solution]

Hi @CalClack,

Try it like this, much easier to read

VariableMeasureValue = 
IF(hasonevalue(dim_Column[Report_column_name]),
SWITCH(VALUES(dim_Column[Report_column_name]),
"C_electric_previous" , SUM('fact_MeterReading'[C electric previous]),
"D_electric_ current" , SUM('fact_MeterReading'[D electric current]),
"E_actual_calculated_units" , SUM('fact_MeterReading'[E actual calculated units]),
"F_previous_units_used" , SUM('fact_MeterReading'[F previous units used]),
"G_Calculated_Electric_Prct" , SUM(fact_MeterReading[G Calculated Electric Prct])*100.00,
"I_Total_Electricty" , SUM('fact_MeterReading'[I Billed Electricity]),
"L_Total_Gas" , SUM('fact_MeterReading'[O Billed Gas]),
"R adjustments" , SUM('fact_MeterReading'[R correction]),
"Total_Billing" , SUM('fact_MeterReading'[S Total Billed])), 
Blank())

Hope this Helps,

Richard 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[jthomson · Accepted solution]

a) Pull MAX(dim_Column[Report_column_name] as a variable at the start of your measure, would allow the if statement to look a lot cleaner. Using switch rather than if would probably be cleaner still

b) What are you trying to display? I can see from your slicer you've got two things selected, MAX(dim_Column[Report_column_name] is only going to have one value, surely?

[CalClack]

"R correction" not being retrieved but "Total Billing" is.  Thanks in advance.