Switch True, getting a value which is not included in total

[Anonymous]

Hi

I am working with a orders dataset, and a order_lines dataset. I have created a measure which is called "Switch True":

Switch True = SWITCH( TRUE(),

[Is_B2B] = "B2B", [Order_Lines Quantity],

[Is_B2B] = "D2C", COUNT(Shopify_Orders[Order_Id]),

CALCULATE(COUNT(Shopify_Orders[Order_Id]), Shopify_Orders[Is B2B] = "D2C") + CALCULATE([Order_Lines Quantity],Shopify_Order_Line_Items[Is B2B] = "B2B"))

Is_B2B tells whether teh order is a B2B order or D2C order, and takes values from the column with the same name:

Is_B2B = SELECTEDVALUE(Shopify_Orders[Is B2B])

Order_line_item_quantity is just a sum of the quantity in the order_lines table:

Order_Lines Quantity = SUM(Shopify_Order_Line_Items[Order_Line_Items_Quantity])

I want to make is so that if "B2B" is chosen in the filter, it shows the sum of order_lines quantity, which it does

When "D2C" is selecte, show the count of order_id, which it does

lastly, if none is selected, show the count of order_id where the order is "D2C" + the order_lines_quantity where the order is a "B2B" order. The numbers are correct, but the problem arises when I want to sort it between orders which has a MMG4 product and orders which has not. No matter if I choose B2B, D2C or both, the 218 orders which does not have  a MMG4 product shows. These should not show when B2B is selected. Below is the code and pictures:

Does anyone have an idea of why those 218 orders are showing, even though I choose B2B in the filter?

The numbers in the switch true column in the visual are correct, even the total of 352 orders in B2B is correct. But it still shows the 218 D2C orders without MMG4.

HAS_MMG4 = If(Shopify_Orders[TOTAL_PRICE_NOK]<0,"Orders with MMG4",IF(CALCULATE(sum(Shopify_Order_Line_Items[HAS_MMG4]))>0,"Orders with MMG4","Orders without MMG4"))

[Anonymous · Accepted solution]

Hi

I found the mistake

It was the Switch(TRUE()) which was incorrect :
The original measure:

Switch True = SWITCH( TRUE(),

[Is_B2B] = "B2B", [Order_Lines Quantity],

[Is_B2B] = "D2C", COUNT(Shopify_Orders[Order_Id]),

CALCULATE(COUNT(Shopify_Orders[Order_Id]), Shopify_Orders[Is B2B] = "D2C") + CALCULATE([Order_Lines Quantity],Shopify_Order_Line_Items[Is B2B] = "B2B"))

In the else statement it says Shopify_Orders[Is B2B] = "D2C". It should have been 

Shopify_Order_Line_Items[Is B2B] = "D2C".

When I changed this, the D2C orders did not show when B2C was selected

[Stachu]

can you share a screenshot of how [Is_B2B] evaluates when only B2B is selected vs when all are selected?

[Anonymous]

Hi

As you can see, I did not but anything explicitly for what it should do when all are selected. I tried, but it did not work, so I just left it empty

[Anonymous]

Important fact:

The filter that is used is "Is B2B" from the order table, not the measure "is B2B" which I sent a screenshot of

[Stachu]

and [Is_B2B] returns "B2B" when B2B is selected in the slicer? not BLANK?

[Anonymous]

Yes, that is correct.

When B2B filter is selected (from the orders table), the measure (shown in the table) shows B2B

[Stachu]

it evaluates to BLANK for "Orders without MMG4", which explains why it goes to the else condition of the SWITCH. SO most likely in Shopify_Orders there are no rows that have both "B2B" and "Orders without MMG4"
If you create this as a dimension table with join with existing column (and put the new one in the slicer), then I think i t should work fine, something like this

IsB2B

B2B

D2C

[Anonymous]

Hi

I creatde the dimension table and used that as a slicer. Still the same result. Any other suggestions?
I have tried to alter the Has_MMG4 measure as well, but no luck yet