Hide empty fact rows while using SCD dimension measure that needs multiple filters

[KaySunset]

Hi all,

I have an issue with the use of a Slowly Changing Dimension (SCD) with multiple fact tables. 

Assume a star schema model, in a very simple form with two fact tables and one product SCD. Within the product dimension, there's a stock value column. I created a measure [ProductStockValue] to be able to show the current product stock value. I need this measure to work in various combinations, e.g. either in combination with Orders or with Invoices - always depending on the current question to analyze. This has been solved with a Switch/IsFiltered combination (seeCROSSFILTER to use one dimension measure with multiple fact analyses  as reference that has been solved perfectly by @AlexisOlson, thanks again).

My problem is that in the above mentioned topic, I have a far easier example than another use case that I have. There is an SCD, where I also use an [IsActive] filter for the dimension-based measure (that is used to display only current Stocks). In combination with the Switch/IsFiltered function this does not work properly: Whenever the stock value at a certain point of time of the order/invoice is not valid any more, it will not be shown.

When I remove the Switch/Isfiltered part I do get correct stock values, but there are many empty fact lines, as all products stock values are always shown.Those I need to be hidden.

Is there any solution to a use of Product SCD with mutliple facts and still have all above functionalities?

Here is a sample file: PBIX

Can anyone help?

BR,

Kathrin

[KaySunset]

Hi Alexis,

thanks for your help again 🙂

That's not exactly what I need, I think my sample file was not build very well to show what I mean - I'll try it with following changes.

The orders 1,2,3 and 7 are all orders of product "A" (table "Initial Situaiton Order").

Product "A" had 10 pieces on stock until 2021-05-31 (highlihted red), which changed to 5 pieces as of 2021-06-01 (highlighted yellow).

The connection between fact and dimension is established via the ProductID (see arrows); as it is a slowly changing dimension, attributes should usually be shown with the as-of situation at a certain date in history. I have added an attribute "Supplier" for reference. Thus, OrderId 1,2 and 7 are shown with supplier "Sup1" that was valid until 2021-05-31 becuase the order date was dated before that; OrderId 3 is shown with supplier "Sup2" that was valid after or on 2021-06-01 (all marked in green rectangle).

In this special case of stock values I need to have a different approach, however. Here, I always need the current as-is situation, which is labeled with the "IsActive"-Flag (1 = current). Thus, I somehow need to keep the above logic but bypass it for this measure - In all four rows in the table "Order - wrong stock values, no empty lines" there should be stock value 5 (as shown with green arrow).

Here you can find the changed file: PBIX

Hope this explanation helps?

[AlexisOlson]

This is hard since the ProductID you're relating tables with doesn't correspond to a single product but a product at a certain time. You might want to re-think your model to make this work more easily.

This is as close as I managed to get. Not pretty.

ProductStockValue_AO = 
VAR ActiveProds =
    CALCULATETABLE (
        VALUES ( 'Product'[ProductID] ),
        FILTER ( ALLEXCEPT ( 'Product', 'Product'[product] ), 'Product'[IsActive] = 1 )
    )
VAR OrderProds = ALLSELECTED ( 'Order'[ProductID] )
VAR InvoiceProds = ALLSELECTED ( 'Invoice'[ProductID] )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Order' ),
            IF (
                NOT ISEMPTY ( 'Order' ),
                CALCULATE (
                    SUM ( 'Product'[StockValue] ),
                    REMOVEFILTERS (),
                    'Product'[ProductID] IN INTERSECT ( ActiveProds, OrderProds )
                )
            ),
        ISFILTERED ( Invoice ),
            IF (
                NOT ISEMPTY ( 'Invoice' ),
                CALCULATE (
                    SUM ( 'Product'[StockValue] ),
                    REMOVEFILTERS (),
                    'Product'[ProductID] IN INTERSECT ( ActiveProds, InvoiceProds )
                )
            ),
        SUM ( 'Product'[StockValue] )
    )

[KaySunset · Accepted solution]

Hi Alexis,

I was afraid that changing the data model would be the best solution 😉 Your suggestion works for the order part, but the invoice table shows a stock value of 10 for all products, also those that should be showing 5. But as you said, it's not pretty and considering I have even more fact tables and this is not the only measure effected, this does not make much sense.

So there's no need to go into further analysis, I'll change the datamodel after all.

This still helped me a lot to dig deeper into DAX - thank you so much!

Best regards,

Kathrin

[AlexisOlson]

Does this work for what you're after?

ProductStockValue_AO =
CALCULATE (
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Order' ), CALCULATE ( SUM ( 'Product'[StockValue] ), 'Order' ),
        ISFILTERED ( 'Invoice' ), CALCULATE ( SUM ( 'Product'[StockValue] ), Invoice ),
        SUM ( 'Product'[StockValue] )
    ),
    'Product'[IsActive] = 1
)