Forum Discussion

KCDG60626's avatar
KCDG60626
Regular Visitor
3 years ago

Cross Sale Analysis

I am trying to create a Cross Sale analysis with an undetermined amount of product groupings. This needs to be a calc column within the fact table that has no attribute relationships. The code below works in Power BI and gives me the output that I am looking for however when I go to add it to my power pivot it returns the error: "Cannot query internal supporting structures for column 'Fact'[Cross sales] becuse they depend on a column,relationship or measure that is not processed. please refresh or recalculate the model"

Here is the code (DAX):
ProductGroups
=
VAR totalCustomers =
VALUES ( 'Fact'[Customer ID] )
VAR AllProducts =
VALUES ( 'Fact'[Line of Business] )
VAR productCombinations =
CROSSJOIN ( totalCustomers, AllProducts )
VAR FilteredCombinations =
FILTER (
productCombinations,
'Fact'[Customer ID] = EARLIER ( 'Fact'[Customer ID] )
&& 'Fact'[Line of Business] <> EARLIER ( 'Fact'[Line of Business] )
)
VAR ProductGroup =
CONCATENATEX ( FilteredCombinations, 'Fact'[Line of Business], ", " )
RETURN
IF ( COUNTROWS ( FilteredCombinations ) > 0, ProductGroup, "No Cross-Sale" )

1 Reply