- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2019 06:11 AM

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

Solved! Go to Solution.

Accepted Solutions

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 01:26 AM

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 a****nyone 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] ) ) ) ) )

All Replies

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-08-2019 06:53 AM

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

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 11:39 PM

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 12:02 AM

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.

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 12:43 AM

Hello @v-juanli-msft,

My data is more or less as follows:

TABLE :

Column1 | Column2 |

A | A1 |

A | A2 |

B | B |

C | C |

OTHERTABLE:

Columnx | Value |

A | 1 |

A1 | 2 |

A2 | 3 |

B | 4 |

C | 5 |

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 01:26 AM

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 a****nyone 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] ) ) ) ) )

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 01:59 AM

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.

## Re: Assign VALUES to VAR from SWITCH statement

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 02:05 AM - last edited Friday

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

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 02:17 AM

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

## Re: Assign VALUES to VAR from SWITCH statement

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Wednesday

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

Does the answers above finally solve your problem?

Best Regards

Maggie