Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello folks.
Let's consider the following table:
Test:
LOAD * INLINE
[
Dimension, Measure
A, 1
A, 0
A, 1
B, 1
B, 0
];
Suppose we want to calculate the sum of "Measure" for dimension A using set analysis:
=Sum({$<Dimension={'A'}>}Measure)
This works OK and calculates the expected value of 2 only if there are no other dimensions selected. However, if we select dimension B the value is still 2 even when we told the formula to take into account the current selection by using the dollar sign.
If we use the IF() function instead:
=Sum(If(Dimension='A', Measure))
The formula correctly displays 2 or 0 depending on the selected dimension.
Is there a way to get the desired behavior using set analysis instead of IF()?
Thanks in advance.
Kind Regards,
Ernesto.
you can use the following set analysis
=sum({$*<Dimension={'A'}> }Measure)
Remove set analysis form your expression
Sum({$<Dimension={'A'}>}Measure)
and add Dimension as a dimension in a straight table
you can use the following set analysis
=sum({$*<Dimension={'A'}> }Measure)
Hi Ramón.
Thank you, your suggestion worked perfectly.
Kind Regards,
Ernesto.
There's a simple explanation:
The set expression explicitly selects Dimension={'A'}, so making selections in Dimension will be overridden by tyeh set expression. The $ sign means use the current selections for everything else.
The If() does not change the selections in any way, so if you select B, then the expression will return 0. This is all by design and predictable.
Hi Sasidhar.
Thank you, your suggestion works but I need to display the value on a texbox, not a straight table.
Kind Regards,
Ernesto.
Hi Jonathan.
Very clear explanation, thank you.
Kind Regards,
Ernesto.
Hi,
Check this attachment. I think set analysis is not required. You can select any value from
Dimension.
You could consider loading the Dimension into two columns and use the second column in your set expression:
LET HidePrefix = '_';
Test:
LOAD Dimension,
Dimension as _Dimension,
Measure
INLINE
[
Dimension, Measure
A, 1
A, 0
A, 1
B, 1
B, 0
];
=Sum ({$<_Dimension={'A'}>} Measure)