Forum Discussion

KaySunset's avatar
KaySunset
Icon for Helper II rankHelper II
4 years ago
Solved

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

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. ...
  • KaySunset's avatar
    KaySunset
    4 years ago

    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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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's avatar
KaySunset
Icon for Helper II rankHelper II
4 years ago

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