Forum Discussion
SSAS Live Connection Measure breaking relationship
When creating a measure when live connected to an SSAS tabular model, simple measures are breaking relationships and causing fields to perform an outer join.
I.E. I want to use a switch function so that when region is 'x', the measure returns 1, 'y' 2, 'z' 3
Initial table
| Sales Order | Region | Sales |
| 1000 | x | 20 |
| 2000 | y | 30 |
| 3000 | z | 40 |
Output:
| Sales Order | Region | Measure | |
| 1000 | x | 20 | 1 |
| 1000 | y | 2 | |
| 1000 | z | 3 | |
| 2000 | x | 1 | |
| 2000 | y | 30 | 2 |
| 2000 | z | 3 | |
| 3000 | x | 1 | |
| 3000 | y | 2 | |
| 3000 | z | 40 | 3 |
Does anyone know why this is happening?
HI OcularPatDown,
I think you need to add one more condition to prevent the formula calculate on not matched records:
Measure= IF ( MAX ( Table[Sales] ) <> blank, SWITCH ( SELECTEDVALUE ( Region[Region] ), "x", 1, "y", 2, "z", 3 ) )Regards.
Xiaoxin Sheng
4 Replies
OcularPatDown , can you share the measure calculations
For this example, I used
test =
SWITCH (
TRUE (),
FIRSTNONBLANK ( Region[Region], TRUE () ) = "x", 1,
FIRSTNONBLANK ( Region[Region], TRUE () ) = "y", 2,
FIRSTNONBLANK ( Region[Region], TRUE () ) = "z", 3
)HI OcularPatDown,
I think you need to add one more condition to prevent the formula calculate on not matched records:
Measure= IF ( MAX ( Table[Sales] ) <> blank, SWITCH ( SELECTEDVALUE ( Region[Region] ), "x", 1, "y", 2, "z", 3 ) )Regards.
Xiaoxin Sheng
