I'm trying to build an "expanded" Basket Analysis, where I include more than one dimension to filter by - works just fine - but additionally I'm also trying to aggregate the result of the basket analysis, and not include "Products" that are the same between the Products and Products Filter table, following the suggestion posted by here.
I have a "working" solution that either aggregates all or a solution that doesn't aggregate when Products and Products Filter are the same, but I am searching for a working solution that achieves both.
My model looks as follows:
where dMovement type is the table I'm trying to aggregate by.
Currently I have these two different measures:
Brand Movement (all) = CALCULATE( DISTINCTCOUNT( 'fact'[customer] ); CALCULATETABLE( SUMMARIZE( 'fact' ; 'fact'[customer] ); ALL(dDate); USERELATIONSHIP('fact'[date] ; 'dDate - Filter'[date] ); ALL(dBrand_customer_date); USERELATIONSHIP('fact'[brands_customer_date] ; 'dBrand_customer_date - filter'[brands_customer_date] ) ) )
Brand Movement Only movement = CALCULATE( DISTINCTCOUNT( 'fact'[customer] ); CALCULATETABLE( SUMMARIZE( 'fact' ; 'fact'[customer] ); ALL(dDate); USERELATIONSHIP('fact'[date] ; 'dDate - Filter'[date] ); ALL(dBrand_customer_date); USERELATIONSHIP('fact'[brands_customer_date] ; 'dBrand_customer_date - filter'[brands_customer_date] ); EXCEPT('dBrand_customer_date - filter' ; dBrand_customer_date ) ) )
They are similar except the EXCEPT function in "Brand Movement Only movement", which achieves calculating when 'dBrand_customer_date - filter' is equal to 'dBrand_customer_date'.
From Example 1 below the correct result I'm searching for would be a total of 4, with only a count of 1 in "Single to Single" - Only the movement "Apple to Orange" in Example 3 and not counting "Apple to Apple" - and a count of 1 in "Multiple to Multiple" - Apple Orange to Apple Orange Banana".
From Example 2, I can see that my "Brand Movement Only movement" measure is able to return this result, but only when the "brands_customer_date" column is filtered by the "dBrand_customer_date" table, and not if I'm only filtering by the "dMovement type" table - I assume this is due to the way filtering works from the table relationships.
The .pbix for the example model can be found here: Example model
How can I achieve the result from my "All Movement" measure that enables correct aggregation, but without counting results I don't consider a movement e.g. 'Apple' to 'Apple'?
I'm unable to figure out if this should be achived by DAX or be altering the data model, so it's more simple.
Thanks in advance.
HI @Barslund ,
It seems like you want to use multiple userelationship function to mapping tables records with specific relationship key.
I'm not so sure how relationship mapped when multiple userelationship functions used in same calculation function.
In my opinion, I' d like to suggest you to break relationship and use measure and variable to manually calculate with selected records.
In addition, you can also try to create a attribute slicer to filter records based on different level.
Please understand that these links are provided with no warranties or guarantees of content changes, and confers no rights.
In my opinion the use of multiple relationships is not the problem I'm trying to solve.
If I use a more simple model aligned with the standard Basket Analysis of Russo & Alberto, all I want to do is be able to aggregate the type of movements.
All I want is for example 1 to aggregate to 8 as Example 2 and 3 does.
But what I can notice from example 2, is that aggregation is possible, as is the case in the "Multiple to Single", but it seems to require the filter context that column 'dBrand_customer_date'[brands_customer_date] provides.
Haven't solved this yet, but would very much like to do so. As displaying all combinations of 'start' and 'end' brands is not a feasible visual solution in the real report I'm trying to create, as the possible combinations is very high.
I'm still very open to all the help I can get.
Hi @Barslund ,
After check with your sample file, I found there are a few all/allexcept function in you measure formulas. Obviously, they will effect calculation on total level.
For your scenario, you can take a look at following link if you want to apply aggregation on these measures:
Measure Totals, The Final Word