cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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.

Johan Vermeulen

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.