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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
youssef123
Frequent Visitor

Comparing Customers' Purchased Products

Dear PowerBI Community,

 

I would greatly appreciate any help with this problem.

 

The relationships between my tables is illustrated below:

CustomerA & CustomerB -> Customers using SK Customer
Customers -> Sales using SK Customer

Sales -> Items using SK Item

* The Items table consists of the column "Brand"

 

9ef01f31ca96336aa513e02c807b7a2d.png

 

In the report below, I compare any two Customers that the user selects from the slicers. The left side of the report is completely disconnected from the right side and vice versa. In the table below each graph, I want to illustrate the brands that the customer is not buying, but the other is. For example, if Customer 1 buys a Ferrari and Customer 2 doesn't, it will show in the table under Customer 2. Of course, this data can only be shown after the user selects the two customers who they wish to compare.

The Customer 1 slicer displays the Customer Name column from the CustomerA table as shown in the image above. The Customer 2 slicer displays the Customer Name column from the CustomerB table.

powerbi_forum.jpg
One of my approaches involved creating a measure to access the SELECTEDVALUE from CustomerA and CustomerB and then finding the Brands as required, however, when I came to apply it to the tables under Customer 1 and 2, I was forced to keep the interaction between the left and the right for the tables. This is because the measure is attempting to capture the selected customers 1 and 2. The problem with this is that the table always showed BLANK as the two slicers were more of less filtering on the same column, which is Customer Name.

I need a solution for this problem as nothing that I tried has worked so far. Thank you!

 

1 ACCEPTED SOLUTION

Hi @youssef123 ,

You can create two measures as below to get it, please find the details in the attachment.

Brands Customer YYY is buying but ZZZ is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _yskitems
                    && NOT ( 'Items'[SK Item] IN _zskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )
Brands Customer ZZZ is buying but YYY is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _zskitems
                    && NOT ( 'Items'[SK Item] IN _yskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )

vyiruanmsft_0-1701162380669.png

 However it is static, if your data refer many other customer comparison. It will need to create another measures to get them. Whether you are trying to get which brands are only purchased by specific customers? Could you please provide your final expected result with backend scenario and calculation logic? It would be helpful to find a suitable solution. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
youssef123
Frequent Visitor

Hello, thanks for your reply. Please see below for a sample dataset and the expected output for the tables.

 

Capture.PNG

Hi @youssef123 ,

You can create two measures as below to get it, please find the details in the attachment.

Brands Customer YYY is buying but ZZZ is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _yskitems
                    && NOT ( 'Items'[SK Item] IN _zskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )
Brands Customer ZZZ is buying but YYY is not = 
VAR _YYY =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "YYY" )
    )
VAR _ZZZ =
    CALCULATE (
        MAX ( 'Customers'[SK Customer] ),
        FILTER ( 'Customers', 'Customers'[Customer Name] = "ZZZ" )
    )
VAR _yskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _YYY )
    )
VAR _zskitems =
    CALCULATETABLE (
        VALUES ( 'Sales'[SK Item] ),
        FILTER ( 'Sales', 'Sales'[SK Customer] = _ZZZ )
    )
VAR _tab =
    SUMMARIZE (
        FILTER (
            'Items',
            'Items'[SK Item]
                IN _zskitems
                    && NOT ( 'Items'[SK Item] IN _yskitems )
        ),
        Items[Brand]
    )
RETURN
    CONCATENATEX ( _tab, 'Items'[Brand], "," )

vyiruanmsft_0-1701162380669.png

 However it is static, if your data refer many other customer comparison. It will need to create another measures to get them. Whether you are trying to get which brands are only purchased by specific customers? Could you please provide your final expected result with backend scenario and calculation logic? It would be helpful to find a suitable solution. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

I am not sure how much i can help but i'd like to try.  Share data in a format that can be pasted in an MS Excel file and on that dummy dataset, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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