Customer's Nearest Neighbours by Revenue

[rohit91]

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

[Jihwan_Kim · Accepted solution]

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],
        " ,"
    )