Forum Discussion
Sperling
Advocate II
Advocate IICount days with filters
I have a fact table with items and a dummy for whether the item is deadstock or not. This is stamped on the items each day, so we can see the history of the items we have. The fact table is conn...
isjoycewang
Solution Supplier
Solution SupplierHi Sperling ,
Attached the demo file. Does it make sense to you?
Count =
VAR _item = SELECTEDVALUE(DimItem[DW_SK_Item])
VAR _min_date = CALCULATE(MAX(DimDate[Date]), DimDeadstock[Deadstock] = "No", REMOVEFILTERS(DimDate[Date]))
RETURN
CALCULATE( COUNT(FactInventory[DW_SK_Date]),
FILTER(ALL(FactInventory), FactInventory[DW_SK_Item] = _item && RELATED(DimDate[Date]) > _min_date))
SperlingHi isjoycewang
Appreciate the response! I attempted to use it in my actual model and realized I made a mistake in the sample dataset.
PBI applied both cross-filter direction to the date dimension, which it wasn't supposed to. After I changed it to single cross-filter direction, the measure was blank.
I've attached the file again here, but in your model you can also just change the cross-filter setting.
https://drive.google.com/file/d/1UO5JbDorQBR-LbaoJD333yLvC02eKF-5/view?usp=drive_link
- lbendlin
Super User