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...
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] ) )
        )
    )
)
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] ) )
        )
    )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB

 

My plan B is using your second approach with a SWITCH.
The issue is that I have 3 filters to apply. I believe it is way cleaner to keep a single measure with the VALUES in 3 variables rather than nesting measures one upon each other.

Thanks in any case.

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Anonymous

    I'm not sure I understand what you mean. Keep in mind though that measures too  can only hold scalars, not tables.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      AlB
      My idea for two filters is as follows:

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

Intended_Measure2 :=
SWITCH ( [SelectMeasure2] ,
 1, CALCULATE (SUM ( [Intended_Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columny] IN VALUES ( TABLE2[Column1] ) ) ),
   2, CALCULATE (SUM ( [Intended_Measure] ), FILTER ( OTHERTABLE, OTHERTABLE[Columny] IN VALUES ( TABLE2[Column2] ) ) )
)


      If I could store the VALUES conditionally I could include this in a single measure. As I cannot, I rather nest measures than defining the 5*5*5 = 125 combinations I would need to define. I did some tests and it seems to work correctly.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi Anonymous

        It doesn't support to use a measure inside a function SUM.

        Does the answers above finally solve your problem?

         

        Best Regards

        Maggie