Functions for conditions having multiple values

[Marjia]

Hello,

What could be used intead of Switch/If function when the result of each condition is not scalar? Thanks in advance!

[Kedar_Pande]

@Marjia 

If 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

[Marjia]

@Kedar_Pande

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"

    )

[PhilipTreacy]

@Marjia 

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

[Marjia]

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"

    )

[OwenAuger]

A general structure you can use to return a conditional table is:

UNION (
    FILTER (
        <TableExpression_1>,
        <Condition1>
    ),
    FILTER (
        <TableExpression_2>,
        <Condition2>
    ),
    ...
)

The conditions <Condition1> etc should be constants within FILTER, and might be evaluated up-front and assigned to variables.

All of the table expressions would naturally have to have the same number of columns. If the columns of the table expressions have different lineage, you may need to apply TREATAS to enforce lineage (if required).