Forum Discussion
Functions for conditions having multiple values
Super UserIf your conditions return tables or arrays but can still be checked against certain conditions, you can leverage SWITCH(TRUE()). This is a common pattern when you need to handle multiple conditions that return non-scalar results.
Result = SWITCH(TRUE(),
[Condition1], Table1,
[Condition2], Table2,
[Condition3], Table3,
DefaultTable
)
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Hello Kedar,
Thnaks for your support. The condition is pretty much like below (partly anonymoused). Could have some typos whilst anonymising but the idea is clear in the code. Switch isn't suitable as the CurrentMonthNo is always a scalar value and so the Switch/If return value only for the current month (e.g. 11) but I want 12 month's value in return. Thanks in advance!
Dynamic_P =
VAR CurrentMonthNo = MAXX(
FILTER('Period', 'Period'[Dynamic_Month] = "Cur"),
'Period'[MonthNumber]
)
RETURN
SWITCH(
TRUE(),
CurrentMonthNo <= 2,
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC1")
),
CurrentMonthNo >= 3 && 'Period'[MonthNumber] <= 5,
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC1"),
FILTER('Period', 'Period'[MonthNumber] <= 2)
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC2"),
FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5, 6, 7, 8, 9, 10, 11, 12})
),
CurrentMonthNo >= 6 && 'Period'[MonthNumber] <= 8,
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC1"),
FILTER('Period', 'Period'[MonthNumber] <= 2)
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC2"),
FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC3"),
FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8, 9, 10, 11, 12})
),
CurrentMonthNo >= 9,
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC1"),
FILTER('Period', 'Period'[MonthNumber] <= 2)
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC2"),
FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC3"),
FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8})
) +
CALCULATE(
[Produced],
FILTER('Version', 'Version'[Version] = "FC4"),
FILTER('Period', 'Period'[MonthNumber] IN {9, 10, 11, 12})
),
"Error: No condition met"
)