Forum Discussion
Functions for conditions having multiple values
Super User
Depends what you want to do. Using a table in SWITCH or IF is not necessarily an issue if you are trying to do something like COUNTROWS.
Please give an example of what you are trying to do.
Regards
Phil
Hi Philip,
Thnaks for prompt action. The condition is pretty much like below (partly anonymoused). Switch isn't suitable as the CurrentMonthNo is always a scalar value and so the Switch/If return value only for the current month but I want 12 month's value in return. Could have some typo now when I anonymoused the code but the idea is clear. 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"
)