none
Managing a SCD2 dimension related to a fact table - SSAS Tabular 2014-2016 RRS feed

  • Question

  • Hi,
    I've a SQL Server data mart having a SCD2 dimension (e.g. a DIM_Products dimension), a fact table (e.g. a Fact_Sales fact table) and a Calendar dimension.
    The SCD2 dimension has a surrogate key (as a primary key), and effective start date and end date. The fact table has a registration date for each new record inserted and the SCD2 dimensione surrogate key. The Calendar dimension has a date for each day in a continuous manner.
    I'm implementing a SSAS Tabular model reading from this SQL Server data mart.
    In the model, I can relate the SCD2 to the fact table.
    I need to select a date for the corresponding calendar filter in order to filter the SCD2 dimension rows having the filtered calendar date comprises between the dimension start date and end date. The SCD2 dimension filter propagates his effects to the fact table.
    E.g., for a specific calendat date I want to filter the DIM_Products dimension to select the valid products respect to the calendar date (dimension start date <= calendar date <= dimension end date).
    In order to solve this issue, I'm thinking to write a DAX expression but I'd like to find a better solution from the point of end-user view.
    Any suggests to me, please? Thanks


    Wednesday, November 20, 2019 7:48 PM

All replies

  • Hi pscorca,

    Thanks for posting here.

    >>E.g., for a specific calendat date I want to filter the DIM_Products dimension to select the valid products respect to >>the calendar date (dimension start date <= calendar date <= dimension end date).

    The July 2017 release of Power BI Desktop includes a new tool function called SELECTED VALUE. If possible, you could try it. Otherwise, you have to use the two common functions (HASONEVALUE and VALUES) to retrieve a value from the filter context.

    Using the SELECTEDVALUE function in DAX

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 2:25 AM