Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@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] ) ) ) ) )
Hi @Anonymous
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 :
Column1 | Column2 |
A | A1 |
A | A2 |
B | B |
C | C |
OTHERTABLE:
Columnx | Value |
A | 1 |
A1 | 2 |
A2 | 3 |
B | 4 |
C | 5 |
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 ( ..... )
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] ) ) ) ) )
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.
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |