Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

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

 

Best regards

Maggie

Anonymous
Not applicable

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

 

 

AlB
Super User
Super User

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
Not applicable

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.

@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
Not applicable

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

@Anonymous

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

Anonymous
Not applicable

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.