Functions for conditions having multiple values

Question

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

owenauger · Answer

A general structure you can use to return a conditional table is:
&nbsp;
&nbsp;
&nbsp;
UNION (
    FILTER (
        &lt;TableExpression_1&gt;,
        &lt;Condition1&gt;
    ),
    FILTER (
        &lt;TableExpression_2&gt;,
        &lt;Condition2&gt;
    ),
    ...
)
&nbsp;
&nbsp;
&nbsp;
The conditions &lt;Condition1&gt; etc should be constants within FILTER, and might be evaluated up-front and assigned to variables.
&nbsp;
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).

philiptreacy · Answer

Marjia&nbsp;
&nbsp;
Depends what you want to do.&nbsp; Using a table in SWITCH or IF is not necessarily an issue if you are trying to do something like COUNTROWS.
&nbsp;
Please give an example of what you are trying to do.
&nbsp;
Regards
&nbsp;
Phil

marjia · Answer

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 =&nbsp;VAR CurrentMonthNo = MAXX(&nbsp;&nbsp; &nbsp; FILTER('Period', 'Period'[Dynamic_Month] = "Cur"), &nbsp;&nbsp; &nbsp; 'Period'[MonthNumber]&nbsp;)&nbsp;&nbsp;RETURN&nbsp;SWITCH(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &lt;= 2,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 3 &amp;&amp; 'Period'[MonthNumber] &lt;= 5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5, 6, 7, 8, 9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 6 &amp;&amp; 'Period'[MonthNumber] &lt;= 8,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC3"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8, 9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 9,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC3"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC4"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; "Error: No condition met"&nbsp; &nbsp; )

kedar_pande · Answer

Marjia&nbsp;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.&nbsp;Result = SWITCH(TRUE(),[Condition1], Table1, [Condition2], Table2, [Condition3], Table3, DefaultTable )💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉Cheers,KedarConnect on LinkedIn&nbsp;

marjia · Answer

Kedar_PandeHello 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)&nbsp; but I want 12 month's value in return. Thanks in advance!Dynamic_P =&nbsp;VAR CurrentMonthNo = MAXX(&nbsp;&nbsp; &nbsp; FILTER('Period', 'Period'[Dynamic_Month] = "Cur"), &nbsp;&nbsp; &nbsp; 'Period'[MonthNumber]&nbsp;)&nbsp;&nbsp;RETURN&nbsp;SWITCH(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &lt;= 2,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 3 &amp;&amp; 'Period'[MonthNumber] &lt;= 5,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5, 6, 7, 8, 9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 6 &amp;&amp; 'Period'[MonthNumber] &lt;= 8,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC3"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8, 9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CurrentMonthNo &gt;= 9,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC1"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] &lt;= 2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC2"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {3, 4, 5})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC3"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {6, 7, 8})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Produced],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Version', 'Version'[Version] = "FC4"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Period', 'Period'[MonthNumber] IN {9, 10, 11, 12})&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; "Error: No condition met"&nbsp; &nbsp; )

Forum Discussion

Functions for conditions having multiple values

5 Replies

Related content

Multiple conditions Index

IF function with multiple conditions

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

DAX Max Function with multiple conditions