Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
arobinson
Frequent Visitor

Var with an If Statement Maybe?

I am looking to do an if statement within a much larger if statement in Power BI. 

 

 This is what the formula would be based on. 

Goal = IF([Attendance Measure] <= 4, 20, IF([Attendance Measure] >= 5, 40, IF([Attendance Measure] = 0, 0)))
 
I need to replace the "If True" spots with the following for all 12 months. If the [Attendance Measure] is 4, I need to cut each of the below months in half. If it is 5 or more, I need to keep them the same. 
 
D1 Booked by Hours Goal = If(
'Monthly Goals'[Month] = "January",
('Monthly Goals'[Booked by Goals] / 24) * .048,
If('Monthly Goals'[Month] = "February",
('Monthly Goals'[Booked by Goals] / 24) * .048,
If('Monthly Goals'[Month] = "March",
('Monthly Goals'[Booked by Goals] / 30) * .048,
If('Monthly Goals'[Month] = "April",
('Monthly Goals'[Booked by Goals] / 24) * .048,
If('Monthly Goals'[Month] = "May",
('Monthly Goals'[Booked by Goals] / 24) * .048,
If('Monthly Goals'[Month] = "June",
('Monthly Goals'[Booked by Goals] / 30) * .048,
If('Monthly Goals'[Month] = "July",
('Monthly Goals'[Booked by Goals] / 24) * .057,
If('Monthly Goals'[Month] = "August",
('Monthly Goals'[Booked by Goals] / 24) * .057,
If('Monthly Goals'[Month] = "September",
('Monthly Goals'[Booked by Goals] / 30) * .057,
If('Monthly Goals'[Month] = "October",
('Monthly Goals'[Booked by Goals] / 24) * .057,
If('Monthly Goals'[Month] = "November",
('Monthly Goals'[Booked by Goals] / 24) * .057,
If('Monthly Goals'[Month] = "December",
('Monthly Goals'[Booked by Goals] / 30) * .057))))))))))))
2 REPLIES 2
az38
Community Champion
Community Champion

@arobinson 

It seems I have even better decision

Goal = 

var denominator = IF(MOD(MONTH('Monthly Goals'[Booked by Goals]), 3) = 0, 30, 24)
var multiplier = IF(MONTH('Monthly Goals'[Booked by Goals]) < 7, 0.048, 0.057)
var D1BookedbyHoursGoal = ('Monthly Goals'[Booked by Goals] / denominator ) * multiplier 

RETURN
IF([Attendance Measure] <= 4, D1BookedbyHoursGoal  / 2, D1BookedbyHoursGoal )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

Hi @arobinson 

try var + switch()

Goal = 

var D1BookedbyHoursGoal = SWITCH('Monthly Goals'[Month]
"January", 
('Monthly Goals'[Booked by Goals] / 24) * .048,
"February",
('Monthly Goals'[Booked by Goals] / 24) * .048,
"March",
('Monthly Goals'[Booked by Goals] / 30) * .048,
"April",
('Monthly Goals'[Booked by Goals] / 24) * .048,
"May",
('Monthly Goals'[Booked by Goals] / 24) * .048,
"June",
('Monthly Goals'[Booked by Goals] / 30) * .048,
"July",
('Monthly Goals'[Booked by Goals] / 24) * .057,
"August",
('Monthly Goals'[Booked by Goals] / 24) * .057,
"September",
('Monthly Goals'[Booked by Goals] / 30) * .057,
"October",
('Monthly Goals'[Booked by Goals] / 24) * .057,
"November",
('Monthly Goals'[Booked by Goals] / 24) * .057,
"December",
('Monthly Goals'[Booked by Goals] / 30) * .057,
0)

RETURN
IF([Attendance Measure] <= 4, D1BookedbyHoursGoal  / 2, D1BookedbyHoursGoal )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors