Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to: Aggregating Basket analysis results

Hi,

 

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.

 

Explanation:

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:

Example_ModelExample_Model

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.

 

ExamplesExamples

 

The .pbix for the example model can be found here: Example model

 

Question:

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.

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

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. 

Understanding DAX Auto-Exist

In addition, you can also try to create a attribute slicer to filter records based on different level.

Dynamic Attributes In A Power BI Report

Please understand that these links are provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@v-shex-msft ,

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.

A more simple model can be found here

 

Example:
Brand_Change_Only.PNG

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 @Anonymous ,

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
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.