Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Using date Dimension with emtpy date

Hey, I need a suggestion.


I have a set of FACT which have a "sale" date and a "ship" date.
So I created in my model two Date Dimensions, one connected to the Sale and one to the Ship.

 

Unfortunately in some cases a product is not shipped, so the Ship date is empty.

 

But if I create this relationship as active, all the rows without a Ship date are filtered out, but I don't want this. I want to see products which has been sold but not yet shipped and I can't make it work.

I know I could make the relationship with the Ship date as inactive, but this generate other issues whenever I want to filter out by ship date, as slicers does not work with inactive relationships.

 

In general, in a dimensional model, you create an "empty" date (like 00/00/0000) and you link these emtpy ones to this "sort of null" row, but PBI does not allow this as dates in the date dim must be contiguous.


Any help?

Thanks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    suggestions?

  • TeigeGao's avatar
    TeigeGao
    Solution Sage

    Hi adetogni,

    Could you please share some sample data and expected result to us for analysis?

    Actually, for empty data, we can use blank() to filter them out.

    Best Regards,

    Teige