Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Assign VALUES to VAR from SWITCH statement

In DAX I want to assign different values to a VAR based on another measure.

My idea was to use a switch but it doesn't work:

 

Intended_Measure := VAR Test = SWITCH( [SelectMeasure],1,VALUES(TABLE[Column1]),2,VALUES(TABLE[Column2]))
RETURN( ... )

If I take a non varing VAR it does work.

Intended_Measure := VAR Test = VALUES(TABLE[Column1])

RETURN( ... )

 

Is there a way to make my first approach work??

 

Thanks

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Anonymous

     

    You're right. It seems IF returns a scalar too. I would then try one of the following. In any case, I'd also be interested in seeing other approaches. Does anyone have other ideas?  It might be a good idea to open up another thread asking for them.

     

    Intended_Measure :=
VAR Test1 =
    VALUES ( TABLE[Column1] )
VAR Test2 =
    VALUES ( TABLE[Column2] )
RETURN
    IF (
        [SelectMeasure] = 1,
        CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN Test1 )
        ),
        IF (
            [SelectMeasure] = 2,
            CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN Test2 )
            )
        )
    )

    or without the VARs:

     

    Intended_Measure :=
IF (
    [SelectMeasure] = 1,
    CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN VALUES ( TABLE[Column1] ) )
    ),
    IF (
        [SelectMeasure] = 2,
        CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN VALUES ( TABLE[Column2] ) )
        )
    )
)

9 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous

     

    SWITCH( ) returns a scalar. You are attempting to return a table. Try with nested IFs:

     

    Intended_Measure :=
VAR Test =
    IF (
        [SelectMeasure] = 1,
        VALUES ( TABLE[Column1] ),
        IF ( [SelectMeasure] = 2, VALUES ( TABLE[Column2] ) )
    )
RETURN
    ( ..... )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello AlB,

      I changed the SWITCH to a nested IF but I run into the same problem.

      I'm using the TEST variable as a condition in the return clause:

       

      Intended_Measure :=
VAR Test =
    IF (
        [SelectMeasure] = 1,
        VALUES ( TABLE[Column1] ),
        IF ( [SelectMeasure] = 2, VALUES ( TABLE[Column2] ) )
    )
RETURN
    ( CALCULATE(SUM([Measure]), FILTER(OTHERTABLE, OTHERTABLE[Columnx] in TEST)) )

       

      I receive the error:

      The function expects a table expression for argument '', but a string or numeric expression was used.

       

      If I use VALUES(TABLE[Column1]) in the filter clause it works correctly.

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Anonymous

         

        You're right. It seems IF returns a scalar too. I would then try one of the following. In any case, I'd also be interested in seeing other approaches. Does anyone have other ideas?  It might be a good idea to open up another thread asking for them.

         

        Intended_Measure :=
VAR Test1 =
    VALUES ( TABLE[Column1] )
VAR Test2 =
    VALUES ( TABLE[Column2] )
RETURN
    IF (
        [SelectMeasure] = 1,
        CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN Test1 )
        ),
        IF (
            [SelectMeasure] = 2,
            CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN Test2 )
            )
        )
    )

        or without the VARs:

         

        Intended_Measure :=
IF (
    [SelectMeasure] = 1,
    CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN VALUES ( TABLE[Column1] ) )
    ),
    IF (
        [SelectMeasure] = 2,
        CALCULATE (SUM ( [Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columnx] IN VALUES ( TABLE[Column2] ) )
        )
    )
)
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous

    Could you show an example of your data and expected output?

     

    Best regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello v-juanli-msft,

       

      My data is more or less as follows:

       

      TABLE : 

       

      Column1Column2
      AA1
      AA2
      BB
      CC

       

      OTHERTABLE:

       

      ColumnxValue
      A1
      A12
      A23
      B4
      C5