counting rows in fact for record in dimension table if no rows exist

[Johan]

Hi,

I can't figure out what dax formula to use.
I have a dimension table ITEM and FACT table holding all inventory changes.
Let's assume ITEMs A, B, C, D, E

And the FACT's:
Date      Item Quantity
1-1-2016   A    1
7-1-2016   B    2
13-1-2016  C    3
19-1-2016  A    4
25-1-2016  B   -1
31-1-2016  C   -1
6-2-2016   D    6

The calculated measure to create is ' number of items without stock' (sum quantity = 0). This must also be calculated backwards, for example on the 31st of january.

The right results are:
- no time filter: 1 (Item E)
- on 31st of janary: 2 (D and E)

Problem I'm encountering is that when you start filtering on date, it automatically filters the FACT's and thus also the ITEM, which make item E dissapear.

Thanks in advance,

Johan Vermeulen

[v-yulgu-msft · Accepted solution]

Hi @Johan,

First, you need to create a temporary table and calendar table:

Table =
UNION (
    'FACT',
    SELECTCOLUMNS (
        'Item',
        "Date", MIN ( 'FACT'[Date] ),
        "Item", 'Item'[Item],
        "Quantity", 0
    )
)

DIMDATE = CALENDAR("2016-01-01","2016-12-31")

Use the date from Dimdate table as slicer. 

To display corresponding Item whose stock is 0, use this measure:

Measure 3 =
VAR VAL =
    SUMX (
        FILTER ( 'Table', [DATE] <= MAX ( 'DIMDATE'[Date] ) ),
        'Table'[Quantity]
    )
RETURN
    IF ( VAL = 0, 0, BLANK () )

To show the number of how many items has no stock, use this measure:

Measure4 =
SUMX (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Date] <= MAX ( DIMDATE[Date] ) ),
        'Table'[Item],
        "sumQuantity", SUM ( 'Table'[Quantity] )
    ),
    IF ( [SumQuantity] = 0, 1, 0 )
)

Thanks,
Yuliana Gu