Forum Discussion

sitm-matt's avatar
sitm-matt
Frequent Visitor
6 years ago
Solved

Distinct count over time with conditions

Hi, I'm looking for some help with a DAX expression.   I'm trying to create a measure that can count the number of unique Items on hand at any given date in time. My fact table represents an inven...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello sitm-matt 

    Give this a try:

    Items With Inventory Balance = 
COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )
sitm-matt's avatar
sitm-matt
Frequent Visitor
6 years ago

Hi jdbuchanan71 

 

I'm afraid the dataset is a little more complicated than that. The Inventory table does not contain balances, only transactions.

 

Here's an example of the Inventory table.

 

When I calculate the InventoryBalance measure, here's the output.

 

Item B doesn't have transactions in Feb-April, but it has an Inventory Balance so I want it counted. Item C has a transaction in February, but it doesn't have Inventory Balance so it shouldn't be counted in that month.

 

Here's the output of your suggested measure.

 

Here's what I'm hoping to get output.

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
6 years ago

Hello sitm-matt 

Give this a try:

Items With Inventory Balance = 
COUNTROWS( FILTER ( VALUES ( 'Item'[ItemCode] ), [InventoryBalance] > 0 ) )
  • sitm-matt's avatar
    sitm-matt
    Frequent Visitor
    6 years ago

    jdbuchanan71  Thank you very much! I was definitely over complicating it in my mind. Your measure works flawlessly! Performance is good over a large dataset too.