Forum Discussion
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
- lbendlinSuper User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523