Forum Discussion
How do I create a measure that will slice correctly on an indirect table dimensions?
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.
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
Super UserIt 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
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
ResultWhile 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.