Filter Context Confusion

[macfuller]

I have a data model and measure where I am reporting on the number of items in stockrooms that were *not* ordered in the last x days. It works, but the Power Pivot table is not paying attention to one of the filters and I don't understand filter context well enough to understand why. Apologies for the long post, but the spreadsheet is massive and proprietary so I will try to illustrate the setup by this description.

In simplified form I have 3 tables: ManagedBy - 1:many - Storeroom - 1:many - OrderHistory.

ManagedBy has
Storeroom Manager
--------- --------
009G Internal
118L Vendor
047E Vendor
Storeroom and ManagedBy are linked by Storeroom

Storeroom has
Business Unit Location Item Unit PrimaryKey
------------- -------- ---- ---- ----------
Hospital 1 009G 25052 Box 009G_25052_Box
Hospital 1 118L 13452 Case 118L_13452_Case
Hospital 2 A100 33245 Roll A100_33245_Roll
Hospital 3 047E 25052 Each 047E_25052_Each
Storeroom IDs are unique across the organization so Business Unit is not needed in the primary key
OrderHistory and Storeroom are linked by PrimaryKey-ForeignKey

OrderHistory has
Business Unit Location Item Unit ForeignKey OrderDate OrderQty
------------- -------- ---- ---- ---------- --------- --------
Hospital 1 009G 25052 Box 009G_25052_Box May 12 3
Hospital 1 118L 13452 Case 118L_13452_Case May 12 4
Hospital 2 A100 33245 Roll A100_33245_Roll May 12 1
In this simple example I want to report on item 25052 in location 047E that does not have any orders associated with it. My measure is:

Zero Orders:=
IF (
ISBLANK (
CALCULATE ( COUNTROWS ( 'OrderHistory' ), 'Order History'[OrderQty] > 0 )
),
0,
CALCULATE ( COUNTROWS ( 'OrderHistory' ), 'Order History'[OrderQty] > 0 )
)
and I filter the pivot table by clicking on the row header to show only the zero items.
(Rows can get into the OrderHistory table without any order qty for various reconciliation purposes which is why I have to test for the OrderQty actually being positive.)

I have a timeline to filter the OrderHistory[OrderDate] field and slicers to filter by Storeroom[Business Unit] and Storeroom[Storeroom], which work great. However, if I add a slicer for the ManagedBy[Manager] field, the pivot table ignores the slicer selection. (Ditto if I add the field directly to the pivot table filter section.)

So, I'm trying to understand why the Manager slicer is being ignored - is it something about the part of the measure that forces the zero values? I am able to filter on the Manager field for most other similar pivot tables so that's my guess. I will also be adding another lookup table at the same level as the ManagedBy table - an item master field that will give me the start date for items, so that I can exclude zero-order items that were added to a storeroom sometime after my earliest selected OrderDate. So I need to resolve this problem before I do that.

Thanks for your help.

 

[Matt Allington]

Is every row in the order history table a single order?

i don't understand why your zero orders measure is like it is. Why not this?

zero orders = calculate(countrows(orders),'order history'[orderqty]<=0)

I would check the relationship between ManagedBy and StoreRoom to make sure it is working. Set up a pivot and a measure that countsrows of the storeroom and see what happens.

 

[macfuller]

Yes, every row is a single order for that storeroom/item/unit combo. A room could stock the same bandages in both Each and Roll units, each would be a single row in OrderHistory if ordered.

I'll have to test your suggested measure, but items with zero orders won't have any rows in the OrderHistory table because they weren't ordered. When I tried similar measures to yours they return blanks for those items, which in turn won't show up in the pivot table. I think your measure would only give me the 41 rows (out of 330,000) with a zero value for OrderQty? Forcing a zero for items without matching records seemed to be the only way to have them show up in the pivot table.

ManagedBy and Storeroom do work to identify vendor-managed units in the table, it's just with that measure that the slicer is ignored. That's what stunned me.

 

[Matt Allington]

items with zero orders won't have any rows in the OrderHistory table because they weren't ordered.

OK, that wasn't clear to me originally.

I would use this measure to work out if there were zero orders

zero orders = calculate(countrows(orders),filter(StoreRoom,calculate(sum('order history'[orderqty]))<=0))

Let me know if it works and if that fixes the slicer issue.

 

[macfuller]

Sorry, your measure is just returning the rows in the Order History table with OrderQty=0 (we don't have negative order quantities, returns are handled differently), not the items in the Storeroom table with *no* rows in the Order History table.

 

[macfuller]

OK, here's something stranger. I simplified my first measure since I didn't care about the items that had orders...

Zero Orders:=IF(ISBLANK(CALCULATE(COUNTROWS('Order History'),'Order History'[PO/MSR Qty] > 0)),0,BLANK())

This gives the right value if I pick an individual location but the slicer selections are "reversed" for lookup table values. To explain, I have slicers for Storeroom[Location] and Storeroom[Business Unit].
In my example, location 047E is in business unit Hospital 3. The Location slicer is "normal" in that all locations are selectable with no other filters, so 047E is selectable. However the Business Unit slicer is "reversed" in that the Hospital 3 business unit is greyed out as if there aren't any records associated with it.

Again, I confirmed that all normal/"positive" measures work with the slicers as expected. By "positive" I mean measures that return values for items that exist in the desired table. My slicers seem to go bonkers (a technical term ) for the measure that is returning values that *aren't* there.

Maybe I'll have to go thru our MSFT rep at work - we've got an NDA/BAA with them.

For now I can remove all slicers other than the single Location and let our hospital directors check on each Storeroom individually. But I sure would like a measure that wasn't tying all of us in knots!

 

[macfuller]

I found another approach to identifying items that are in lookup tables but not in fact tables. It's more setup work but the tables and measures will have multiple uses and will work normally with slicers. I'm posting it here in case anyone else needs something similar.

I had built a histogram capability prior to this question with the tables and measures below, and my current intern realized the measure would work for my latest purpose as well. First step is to create an unconnected table in the data model. Here is the PowerQuery code I use.
-----------------------
let
/* This query will build categories by incrementing integers by +1 to the TotalBins number
/* Query Name is "Histogram"
/* Set the TotalBins to as many histogram groups as desired.
/* History data is 91 days so set TotalBins to 90, although no items are currently ordered daily
TotalBins = 90,
ListNum = List.Generate(()=>0, each _ <= TotalBins, each _ + 1),
ConvertToTable = Table.FromList(ListNum, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenameToBin = Table.RenameColumns(ConvertToTable,{{"Column1", "Bins"}}),
ChangeColType = Table.TransformColumnTypes(RenameToBin,{{"Bins", Int64.Type}}),
MaxBin = Record.Field(Table.Max(ChangeColType, "Bins"), "Bins"),
AddColMinVal = Table.AddColumn(ChangeColType, "MinVal", each [Bins], type number),
AddColMaxVal = Table.AddColumn(AddColMinVal, "MaxVal", each if [Bins] = MaxBin then 99999 else [Bins] + 1, type number)
in
AddColMaxVal
-----------------------
The associated measures are:

Distinct SKUs:=DISTINCTCOUNT ( Storeroom[PrimaryKey] )

History Line Count Total:=CALCULATE ( COUNTROWS ( 'OrderHistory' ), 'Order History'[OrderQty] > 0 )

Dynamic Order Freq:=CALCULATE (
[Distinct SKUs],
FILTER (
VALUES ( 'Storeroom'[PrimaryKey] ),
COUNTROWS (
FILTER (
Histogram,
[History Line Count Total] >= Histogram[MinVal]
&& [History Line Count Total] < Histogram[MaxVal]
)
)
)
)
----------------------
I can then put Dynamic Order Freq into a pivot table and list the items and units of measure for the items. Putting a slicer on the Histogram[Bins] field and setting it to zero gives me every item not ordered.

For the original histogram purpose if Histogram[Bins] is used as the row value in a pivot table and Dynamic Order Freq as the value this gives the frequency of items ordered within the slicer and timeline filters you need. This is very handy for identifying the % of slow-moving items (e.g. ordered less than once a week) and whether we can pull those items from the storeroom into a central depot, and comparing storerooms of similar items across our business units.