topic Customer's Nearest Neighbours by Revenue in DAX Commands and Tips

Customer's Nearest Neighbours by Revenue

rohit91 — Sun, 08 Sep 2024 17:31:44 GMT

Hi Experts!!

I've one question regarding finding nearest 4 neighbours of Customer by revenue and displaying them in the same text box.

It could be neighbour who have revenue just more than the selected customer or could be less than selected.

Ideal is to have 2 neighbours having higher revenue and other 2 having lower revenue. Sample data is provided below

Example 1
Selection of Cust C value should yield below as Neighbours
Cust A, Cust B, Cust D and Cust E

Example 2
Selection of Cust B should yield below as Neighbours
Cust A, Cust C, Cust D and Cust E

Example 3
Selection of Cust E should yield below are Neighbours
Cust C, Cust D, Cust F and Cust G

Sample Data is as below. Again stating we need to use Revenue to identify the nearest neighbours

Customer ID	Customer Name	Revenue
1	Cust A	1000
2	Cust B	900
3	Cust C	800
4	Cust D	700
5	Cust E	600
6	Cust F	500
7	Cust G	400
8	Cust H	300
9	Cust I	200

Re: Customer's Nearest Neighbours by Revenue

Jihwan_Kim — Sun, 08 Sep 2024 17:49:57 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result select: = VAR _Slicer = MAX ( 'Customer slicer'[Customer Name] ) VAR _T = ADDCOLUMNS ( ALL ( Data[Customer ID], Data[Customer Name] ), "@Revenue", [Revenue total:], "@SelectedRevenue", CALCULATE ( [Revenue total:], Data[Customer Name] = _Slicer, REMOVEFILTERS () ) ) VAR _Diff = FILTER ( ADDCOLUMNS ( _T, "@Diff", ABS ( [@Revenue] - [@SelectedRevenue] ) ), Data[Customer Name] <> _Slicer ) VAR _NearestCustomer = SUMMARIZE ( WINDOW ( 1, ABS, 4, ABS, _Diff, ORDERBY ( [@Diff], ASC ) ), Data[Customer Name] ) RETURN CONCATENATEX ( FILTER ( Data, Data[Customer Name] IN _NearestCustomer ), Data[Customer Name], " ," )