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
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"
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.
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!
Solved! Go to 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], "," )
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
Hello, thanks for your reply. Please see below for a sample dataset and the expected output for the tables.
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], "," )
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
98 | |
89 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |