Data modeling - create a relationship and filter a dimension

[ellac]

Hi,

I have an issue that I can't wrap my head around regarding the relationship between a dimension table and a fact table.

Background:

• We have a data model with a star schema structure. Let's make it very simple for this example - we have a Product table (dimension) and a Sales table (fact) linked by a relationship between the column called "ArticleID".
• The Product table has a unique product per row, and the Sales table can have several rows with the same product. Nothing weird so far.
• We then have a Markdown table which consists of Markdown history for every product i.e. one product can exist on multiple rows. For example, product A has had three different markdowns, resulting in three rows in the table.
• Not all products in the Product Table exist in the Markdown table.

The problem:

Let's say we have a Markdown called "Summer Markdown" consisting of 200 products, i.e. 200 products got a reduced price during the "Summer Markdown". I want to be able to see what the Sales are for these 200 products. Therefore, I want to filter the Product table from the Markdown table '(when I select "Summer Markdown"), see the picture below. And this doesn't work, except if we break the star model structure and thus significantly degrade the performance of the model.

So far, I've created a hidden many-to-many relationship (had to make a many-to-many relation in Tabular Editor) between the Markdown table and the Product table (Markdown filtering Product), then enabled it in specific measures. But this makes both the report and model REALLY slow.

My plan B is to remake my Markdown table to be a dimension and then in some way connect it directly to the Sales table, so I don't have to go "over" the Product table.

Does anyone have any ideas on how to solve this? And not decreasing the model performance. Let me know if I was unclear 🙂

/Ella

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales: =
VAR _ProductByMarkdownTypeByDate =
    SUMMARIZE (
        GENERATE (
            markdown,
            FILTER (
                'calendar',
                'calendar'[Date] >= markdown[markdown_startdate]
                    && 'calendar'[Date] <= markdown[markdown_enddate]
            )
        ),
        markdown[article_id],
        'calendar'[Date]
    )
RETURN
    IF (
        ISFILTERED ( markdown_type[markdown_type] ),
        CALCULATE (
            SUM ( sales[sales_amount] ),
            TREATAS (
                _ProductByMarkdownTypeByDate,
                'product'[article_id],
                'calendar'[Date]
            )
        ),
        SUM ( sales[sales_amount] )
    )

[ellac]

Wow @Jihwan_Kim , this is really the solution that I need! Thanks A LOT!

[DataInsights]

@ellac,

You should be able to create a one-to-many relationship between Product and Markdown (join on ArticleID). Then create a measure like the one below. The CROSSFILTER function allows bidirectional filtering within the scope of the measure.

Amount =
CALCULATE (
    SUM ( Sales[Amount] ),
    CROSSFILTER ( Markdown[ArticleID], Product[ArticleID], BOTH )
)

[mickey64]

For your reference.