How do I create a measure that will slice correctly on an indirect table dimensions?

[asgerlarsen]

Imagine this:

• I have 3 tables; 1 Product dimension table, 1 Customer dimension table and 1 Sales fact table.
• Both the Product and Customer tables have a one directional relationship to the Sales Table, meaning they are filtering the Sales table and not the other way around.
• There is no direct relation between the Product and Customer table.

Picture for illustration, not actually my datamodel.

I want to create a measure that counts the number of Holding Companies, a dimension with lower granularity than the customer_key dimension.

Here are the problems:

• Since the Product and Customer tables don't filter each other, the above measure will not slice correctly in the contex of the Product table dimensions and will end up showing the total count of Holding Companies.
• The sales table contains billions of rows, because of this, I have to use either direct query or mixed storage mode.
• Creating calculated columns in the fact table will drastically reduce the performance of the report, so I want to avoid doing this.
• Futhermore, I want to avoid using bi directional relationships since this will also impact performance immensly.
  

Under these restrictions, is it possible to write the Holding Company Count measure that will slice the correctly on the product dimensions?

[asgerlarsen · Accepted solution]

I have tried the CROSSFILTER() function, and the performance doesn't seem as bad as I feared, so I will consider this problem solved for now.

I used it in the same way the bi-directional relationship guide illustrates:

Count of Holding Companies =
CALCULATE(
    DISTINCTCOUNT(Customer[Holding Company]),
    CROSSFILTER(
        Customer[CustomerId],
        Sales[CustomerId],
        BOTH
    )
)

This way, when the Product table filters the Sales table, it will also filter the Customer table in the measure.

[OwenAuger · Accepted solution]

It would be interesting to compare the "related distinct count" pattern here.

It should be logically equivalent to your measure, but I would be interested in how performance compares.

In this case it would be:

Count of Holding Companies =
VAR HoldingCompaniesFromSales =
    SUMMARIZE ( Sales, Customer[Holding Company] )
VAR Result =
    SUMX ( HoldingCompaniesFromSales, 1 )
RETURN
    Result

[asgerlarsen]

I did some testing, and it seems like the 'related pattern' performed twice as good in my senario!

After clearing cashe in the desktop file, I ran the performance analyser and got these two results:

The 'Related pattern' uses your sugestion of SUMMARIZE alongside SUMX:

Count of Holding Companies =
VAR HoldingCompaniesFromSales =
    SUMMARIZE ( Sales, Customer[Holding Company] )
VAR Result =
    SUMX ( HoldingCompaniesFromSales, 1 )
RETURN
    Result

While the 'Crossfilter' uses the CROSSFILTER function:

Count of Holding Companies =
CALCULATE(
    DISTINCTCOUNT(Customer[Holding Company]),
    CROSSFILTER(
        Customer[CustomerId],
        Sales[CustomerId],
        BOTH
    )
)

I have marked your suggestion as the a solution.

[asgerlarsen · Accepted solution]

I have tried the CROSSFILTER() function, and the performance doesn't seem as bad as I feared, so I will consider this problem solved for now.

I used it in the same way the bi-directional relationship guide illustrates:

Count of Holding Companies =
CALCULATE(
    DISTINCTCOUNT(Customer[Holding Company]),
    CROSSFILTER(
        Customer[CustomerId],
        Sales[CustomerId],
        BOTH
    )
)

This way, when the Product table filters the Sales table, it will also filter the Customer table in the measure.