ALL() isn't working to "remove a filter" in DAX; relationship issue?

Question

Basic premise:

• 'Orders' are comprised of items from multiple 'Zones'.
• Customers can call in for 'Credits' (refunds) on botched 'Orders'.
• There is a true many-to-many relationship here, because one order could have multiple credits called in at different times; similarly, a customer can call in once regarding multiple orders (generating only one credit memo).

• 'Credits' granularity is at the item level, i.e.

  CREDIT  |   SO      |   ITEM    |   ZONE    |   CREDAMT
  -------------------------------------------------------
  42      |   1       |   56      |   A       |   $6
  42      |   1       |   52      |   A       |   $8
  42      |   1       |   62      |   B       |   $20
  42      |   2       |   56      |   A       |   $12
  

• 'Order Details' granularity is at the zone level, i.e.

  SO      |   ZONE    |   DOL_AMT
  -------------------------------
  1       |   A       |   $50
  1       |   B       |   $20
  1       |   C       |   $100
  2       |   A       |   $26
  

• I have a 'Zone' filter table that helps me sort things better and roll up into broader categories, i.e.

  ZONE    |   TEMP    |   SORT
  -------------------------------
  A       |   DRY     |   2
  B       |   COLD    |   3
  C       |   DRY     |   1
  

What I need:

• I want a pair of visuals for a side by side comparison of order total by zone next to credit total by zone.

What's working:

• The 'Credits' component is easy, CreditTotal = abs(sumx(Credits,Credits[CREDAMT])) with Zone as a legend item.

• I have a ORDER component that works when the zone is in the credit memo

  Order $ by Zone =
  CALCULATE (
    SUM ( 'Order Details'[DOL_AMT] ),
    USERELATIONSHIP ( 'Order Details'[SO], Credits[SO] ),
    ALL ( Credits[CreditCategory] )
  )
  

My issue:

• Zones that didn't have a credit against them won't show up. So instead of

  CREDIT  |   ZONE    |   ORDER $ BY ZONE
  ----------------------------------
  42      |   A       |   $76
  42      |   B       |   $20
  42      |   C       |   $100
  

  I get

  CREDIT  |   ZONE    |   ORDER $ BY ZONE
  ----------------------------------
  42      |   A       |   $76
  42      |   B       |   $20
  

I have tried to remove this filter by tacking on ALL(Zones[Zone]) and/or ALL('Order Details'[Zone]), but it doesn't help, presumably because it is reporting "all zones" actually found in the 'Credits' table. I'm hoping there's some way to ask it to report all zones in the 'Order Details' table based upon SOs in the 'Credits' table.

In case it helps, here's how the relationships are structured; as an aside, I've tried mixing and matching various combinations of active/inactive, single vs. bidirectional filtering, etc., but the current configuration is the only one that seems to remotely work as desired.

I'm grateful for any suggestions; please let me know if anything is unclear. Thank you.

Answer 1

I was able to get it to work by using 'Order Details'[Zone] rather than Zones[Zone] in the table visual and this measure:

Order $ by Zone = 
CALCULATE (
  SUM ( 'Order Details'[DOL_AMT] ),
  USERELATIONSHIP ( 'Order Details'[SO], Credits[SO] )
)

---

Notice that regardless of your measure, there is no row in Credits corresponding to zone C, so it doesn't know what to put in the CREDIT column unless you tell it exactly how.

If you remove the CREDIT dimension column, then you don't need to swap tables as I suggested above. You can just use the measure above and then write a new measure for the CREDIT column instead:

CreditValue =
CALCULATE(
    VALUES(Credits[CREDIT]),
    ALL(Credits),
    Credits[SO] IN VALUES('Order Details'[SO])
)