Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
2 years ago
Solved

hi

hi
Tuesday

Hello,

 

Here I have a few sample data.

 

C_IDC_PHONEB_POINT
16015161622020151.2176
60154161622020744.0992
6002113161952261.9386
601211316195223452.1312
9243213228135550.1
925021322813550
90174132281355619.3274
60156140400500664.8464
160151404005000
130031404005000

 

C_ID--- Customer ID is unique

C_PHONE--- Customer Phones are sometimes duplicates or more than even two time

B_POINT--- got Point balance is according to customer ID and Customer Phone number

 

What I need is using above table,

1) I want to keep the Customer's Phone number which has the highest Balance Point (Remove Duplicate mobile number as per the highest Balance Point)

Expected Outcome for Highest using abovetable, 

C_IDC_PHONEB_POINT
60154161622020744.0992
601211316195223452.131
90174132281355619.3274
60156140400500664.8464

 

2) I want to keep the Customer's Phone number which has the lowest Balance Point (Remove Duplicate mobile number as per lowest Balance Point)

Expected Outcome for Lowest using above table, 

C_IDC_PHONEB_POINT
16015161622020151.2176
6002113161952261.9386
925021322813550
160151404005000
130031404005000

 

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi abc_777 

     

    You can add an intermediate table to have highest and lowest balance point for each phone number first. 

    Table 2 = SUMMARIZE('Table','Table'[C_PHONE],"max_point",MAX('Table'[B_POINT]),"min_point",MIN('Table'[B_POINT]))

    Then create two measures as below. Add them to the table visuals filter pane separately and set the value to 1. 

    Max Filter = IF(SELECTEDVALUE('Table'[B_POINT])=MAX('Table 2'[max_point]),1,0)
    Min Filter = IF(SELECTEDVALUE('Table'[B_POINT])=MAX('Table 2'[min_point]),1,0)

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi abc_777 

     

    I realize that my previous solution is not very efficient with more data. Its performance is bad and may hit the data point limit in a visual. Please try a new solution with below measures. And you don't have to create the intermediate table. 

    MaxFilterSheet2 = 
var _maxPoint = CALCULATE(MAX(Sheet2[B_POINT]),ALLEXCEPT(Sheet2,Sheet2[C_PHONE]))
return
IF(SUM(Sheet2[B_POINT])=_maxPoint,1)
    MinFilterSheet2 = 
var _minPoint = CALCULATE(MIN(Sheet2[B_POINT]),ALLEXCEPT(Sheet2,Sheet2[C_PHONE]))
return
IF(SUM(Sheet2[B_POINT])=_minPoint,1)

     Please see the table visuals on Page 2 in the attached pbix.

     

    In addition, there are some whitespaces in the sample data that causes errors when changing data types, so I remove these whitespaces with Power Query Editor. You can find the detailed steps in the pbix. Hope this would be helpful. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    abc_777 , try these two measures

     

     

    M1 = calculate(max(Table[B_POINT]), filter(allselected(Table), Table[C_ID] = max(Table[C_ID]) ))


    M2 = calculate(Sum(Table[B_POINT]), keepfilter(topn(5,allselected(Table[C_ID]), [M1], desc))) + calculate(Sum(Table[B_POINT]), keepfilter(topn(5,allselected(Table[C_ID]), [M1], asc)))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi abc_777 

     

    You can add an intermediate table to have highest and lowest balance point for each phone number first. 

    Table 2 = SUMMARIZE('Table','Table'[C_PHONE],"max_point",MAX('Table'[B_POINT]),"min_point",MIN('Table'[B_POINT]))

    Then create two measures as below. Add them to the table visuals filter pane separately and set the value to 1. 

    Max Filter = IF(SELECTEDVALUE('Table'[B_POINT])=MAX('Table 2'[max_point]),1,0)
    Min Filter = IF(SELECTEDVALUE('Table'[B_POINT])=MAX('Table 2'[min_point]),1,0)

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!