cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Johan Regular Visitor
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.

 

 

Thanks in advance,

Johan Vermeulen

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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 () )


2.PNG

 

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 )
)

4.PNG

 

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.
1 REPLY 1
Community Support Team
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 () )


2.PNG

 

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 )
)

4.PNG

 

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.