Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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?
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!
Proud to be a Super User!
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
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
All Report_column selected but "R_correction" is the only one not being loaded.
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!
Proud to be a Super User!
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?
"R correction" not being retrieved but "Total Billing" is. Thanks in advance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |