Multiple VAR inside a SWITCH function

[Kev59]

Dear all

I would like to know if it's possible to include multiple VAR inside a switch function to make them dynamic.

I would like to adapt this code with a switch function for the selectedvalue of a segment:

VAR __BaseTable = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Sold To],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table = 
        ADDCOLUMNS(
            __BaseTable,
            "Sold-to", MAXX(FILTER('CDD - Account Grouping', [CDD ERP Reference Id] = [Sold To]),[Sold To & Name]),
            "Rank", RANKX(__BaseTable, [Revenues],,DESC)
        )
VAR __Result =
        SELECTCOLUMNS(
            FILTER(__Table, [Rank] <= 100),
            "Username", [Sold-to],
            "Sum", [Revenues]
        )
RETURN
    __Result

the result of my segment selection is the code below:

VAR _Selection= SELECTEDVALUE('_Dynamic Herarchy'[_Dynamic Herarchy Commande])

I would like to make the calculations according to the choice:

• if the _Selection is "1", I would like to use the code:

AR __BaseTable = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Sold To],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table = 
        ADDCOLUMNS(
            __BaseTable,
            "Sold-to", MAXX(FILTER('CDD - Account Grouping', [CDD ERP Reference Id] = [Sold To]),[Sold To & Name]),
            "Rank", RANKX(__BaseTable, [Revenues],,DESC)
        )
VAR __Result =
        SELECTCOLUMNS(
            FILTER(__Table, [Rank] <= 100),
            "sortie 1", [Sold-to],
            "Sortie 2", [Revenues]
        )
RETURN
    __Result

• if the _Selection is "2", I would like to use the code:

VAR __BaseTable = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Product Code],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table = 
        ADDCOLUMNS(
            __BaseTable,
            "Product", MAXX(FILTER('Products', [Product Code] = [Product Code]),[Product Code]),
            "Rank", RANKX(__BaseTable, [Revenues],,DESC)
        )
VAR __Result =
        SELECTCOLUMNS(
            FILTER(__Table, [Rank] <= 100),
            "sortie 1", [Product],
            "Sortie 2", [Revenues]
        )
RETURN
    __Result

is there a way to that easilly and properly?

Many thanks in advance

[Kev59]

Hi @Anonymous  @PhilipTreacy 

Sorry, I'm not confortable with DAX, so it's not clear for me.

I tired to create a "dynamic" table with this code:

Table = 
VAR _Selection = SELECTEDVALUE('_Dynamic Herarchy'[_Dynamic Herarchy Commande])
// filtre par produits
VAR __BaseTable1 = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Material Code],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table1 = 
        ADDCOLUMNS(
            __BaseTable1,
            "Product", MAXX(FILTER('Products', [Product Code] = [Material Code]),[Product]),
            "Rank", RANKX(__BaseTable1, [Revenues],,DESC)
        )
VAR __Result1 =
        SELECTCOLUMNS(
            FILTER(__Table1, [Product] <> BLANK()),
            "sortie 1", [Product],
            "Sortie 2", [Revenues],
            "sortie 3",[Rank]
        )

// filtre par Type de Produits

   VAR __BaseTable2 = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Material Code],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table2 = 
        ADDCOLUMNS(
            __BaseTable2,
            "Product Type", MAXX(FILTER('Products', [Product Code] = [Material Code]),[Attribute 2]),
            "Rank", RANKX(__BaseTable2, [Revenues],,DESC)
        )
VAR __Result2 =
        SELECTCOLUMNS(
            FILTER(__Table2, [Product Type] <> BLANK()),
            "sortie 1", [Product Type],
            "Sortie 2", [Revenues],
            "sortie 3",[Rank]
        )  

// filtre par Sold to

   VAR __BaseTable3 = 
        SUMMARIZE(
            'CDD - Invoiced Sales',
            [Sold To],
            "Revenues", SUM('CDD - Invoiced Sales'[Net Sales USD])
        )

VAR __Table3 = 
        ADDCOLUMNS(
            __BaseTable3,
            "Sold to & Name", MAXX(FILTER('CDD - Account Grouping', [Sold To] = [Sold To]),[Sold To & Name]),
            "Rank", RANKX(__BaseTable3, [Revenues],,DESC)
        )
VAR __Result3 =
        SELECTCOLUMNS(
            FILTER(__Table3, [Sold to & Name] <> BLANK()),
            "sortie 1", [Sold to & Name],
            "Sortie 2", [Revenues],
            "sortie 3",[Rank]
        )  


RETURN
    SWITCH(
        TRUE(),
        _Selection = 1,
        __Result1,
        _Selection=2,
        __Result2,
        _Selection=3,
        __Result3
    )

But I have the "scalar" issue.

BR

[Anonymous]

Thanks for PhilipTreacy's concern about this issue.

Hi, @Kev59 

I am glad to help you.

Switch can compute an expression based on a list of values and return one of several possible result expressions. This function can be used to avoid having multiple nested IF statements.

In your DAX, you want the SWITCH function to return a different table based on the value of _Selection, that is, the SELECTCOLUMNS function in each branch creates a new table and returns that table. Eventually, the __Result variable will contain the table computed based on the value of _Selection.

However, this can be problematic in practice, where the expression references multiple columns, and multiple columns cannot be converted to scalar values.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @Kev59 

Write the code like this

VAR __BaseTable_1 = .................

VAR __Table_1 = ..............

VAR __Result_1 = ..............

VAR __BaseTable_2 = .................

VAR __Table_2 = ..............

VAR __Result_2 = ..............

VAR _Selection = SELECTEDVALUE('_Dynamic Herarchy'[_Dynamic Herarchy Commande])

RETURN

   IF(_Selection = 1, __Result_1, __Result_2)

Note that I am assuming that _Selection will only ever be 1 or 2.  If there are other values you can use a SWITCH statement of nested IF to test for each value

Regards

Phil

[Kev59]

Hi Philip

Thanks for you answer. I didn't mention that but there are more than 3 possibilities....

that why I wouldn't use nested IF (to hard to read) and to prefere the SWITCH option but I would like to limit the VAR declaration (as they are all the same template, maybe there is a way to use only three?)