Ignore relationship on single case of switch

[orlando9427]

Hi,

I'm trying to apply differents filtering criteria depending on sales division, thanks to amitchandak and the SUMX/SWITCH functions I'm able to apply as intended, but one division generates a problem.

My model contains three tables as follows:

DIVISION	DIVISION_NAME
1	B2B
2	B2C
3	SHOP

SALESMAN	DIVISION_ID	SALESMAN_NAME
1	1	John
2	2	Peter
3	1	Claire

TOTAL_PRICE	SALESMAN_ID	IN_SHOP	ARTICLE	A	B
1000	1	Y	A	1	1
2000	1	N	B	2	2
1500	2	Y	C	2	2
2500	3	Y	D	1	2
1000	-1	Y	E	1	1

IDs are linked in a one to many relationship, so I can graph sales per division and per salesman using this code.

MTD = SUMX(
   VALUES(DIVISIONS[DIVISION]), 
   SWITCH(
      DIVISIONS[DIVISION], 
      1, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[A] = 1)),
      2, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[A] = 2 && SALES[B] = 2)),
      3, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[IN_SHOP] = "Y"),
      0
   )
)

The SHOP division can be generated by division B2B and B2C and also in its own, but both divisions are filtered when graphed vs division and salesman, also tried to disable the relationship and add USERELATIONSHIP on case 1 and 2 but it generate a constant value that is added to all division, is there a way to disable relationship just in case 3?

The intended behavior is to get the following.

DIVISION	SALES
B2B	3500
B2C	1500
SHOP	6000

Thanks in advantage

[amitchandak]

@orlando9427 , Modify Measure as , file is attached with correct reltionship

You can use crossjoin with None to disable the join

Create a measure

MTD = 
Var _tab = ALLSELECTED(SALESMAN[SALESMAN])
return
SUMX(
   VALUES(DIVISION[DIVISION]), CALCULATE(
   SWITCH(
      max(DIVISION[DIVISION]), 
      1, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[A] = 1 && SALES[SALESMAN_ID] in _tab && SALES[IN_SHOP] = "Y")),
      2, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[A] = 2 && SALES[B] = 2 && SALES[SALESMAN_ID] in _tab && SALES[IN_SHOP] = "Y")),
      3, CALCULATE(SUM(SALES[TOTAL_PRICE]),
         FILTER(SALES, SALES[IN_SHOP] = "Y")),
      0
   )
))