Forum Discussion

KaySunset's avatar
KaySunset
Helper 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
Super User
4 years ago

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
)