cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

1 ACCEPTED SOLUTION

@JonArgandona

 

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] ) )
        )
    )
)

View solution in original post

9 REPLIES 9
Community Support
Community Support

Hi @JonArgandona

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

 

Best regards

Maggie

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

 

 

Super User III
Super User III

Hi @JonArgandona

 

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
    ( ..... )

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.

@JonArgandona

 

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] ) )
        )
    )
)

View solution in original post

@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.

@JonArgandona

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

@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.

Hi @JonArgandona

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

Does the answers above finally solve your problem?

 

Best Regards

Maggie

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors