Forum Discussion

rohit91's avatar
rohit91
New Member
2 years ago
Solved

Customer's Nearest Neighbours by Revenue

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 IDCustomer NameRevenue
1Cust A1000
2Cust B900
3Cust C800
4Cust D700
5Cust E600
6Cust F500
7Cust G400
8Cust H300
9Cust I200

 

DAX
power bi
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

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

     

     

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

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