Forum Discussion

mg00842082's avatar
mg00842082
Regular Visitor
2 years ago
Solved

Need urgent: Same customer ID With different First and Last Names Should be display in table visual

Hi All.

 

Data in report:

 

Requirement:

1. 10002001 - This Customer ID is available in Row 1 and Row 2 in report. The First Name, Last Name Address 1, Address 2, Address 3 are different this kind of records need to display and Gender and DOB is same.

 

2. For Example Customer ID - 10008222 is having four records, In this first 3 records is having same data in all the fields and last one is different in this case i want to display the below two records only.

 

(Note : I have many Customer id's in report which is not required to display in report. it means  First Name, Last Name, Address 1, Address 2, Address 3 is matching with same Customer ID's)

 

This is the requirement from the customer which the required data need to display in the table visual of the report.

Expected Result:

 

 

In the above screenshot Expected result is to display Same Customer ID - 10002001 and it will be display which First Name, LastName, Address 1, Address 2, Address 3 Should be different and Gender and DOB remains same.

 

Below is the scenario example:

 

Sample Data: Sample Records provided below to work on to test in power BI:

Customer ID Policy Number First Name Last Name Gender DOB Address 1 Address 2 Address 3
10008001 45678 kumar Krishna M 12/11/1994 555 222 111
10008001 23456 Krishna Gopala M 12/11/1994 444 333 111
10008001 87993 Gopal Krish M 12/11/1994 234 456 456
10008001 87993 Gopal Krish M 12/11/1994 234 456 456
10008001 90080 Gopal Krish M 12/11/1994 234 456 456
10002001 11223 Vinay Kumar M 10/8/1995 3/2/301 ABC 123
10002001 22334 Rupesh sai M 10/8/1995 2/301 def 456
10004001 33445 krishna Kumar M 8/7/1995 4/334 ghi 789
10004001 43386 suresh Kumar M 8/7/1995 2/001 jkl 901
10004001 44556 suresh Kumar M 8/7/1995 2/001 jkl 901
10005100 47890 Ramesh sai M 8/8/1996 DEF 111 8/222
10005100 87903 prashanth roy M 8/8/1996 ghi 888 9/111
10008222 24794 nikhil shetty M 11/11/1992 GHI 333 12月11日
10008222 82395 Baba sab M 11/11/1992 GHI 444 11月12日
10008222 88772 Baba sab M 11/11/1992 GHI 444 11月12日
10008222 99772 Baba sab M 11/11/1992 GHI 444 11月12日

 

 

Regards,

Krishna.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from danextian , please allow me to provide another insight: 
    Hi  mg00842082 ,

     

    Here are the steps you can follow：

    1. Add Column – Index Column – From 1.

    2. Create measure.

    Count =
CALCULATE(COUNT('Table'[Customer ID]),ALLEXCEPT('Table','Table'[Customer ID],'Table'[First Name],'Table'[Last Name],'Table'[Address 1],'Table'[Address 2],'Table'[Address 3]))
    Flag =
var _minindex=MAXX(FILTER(ALL('Table'),[Customer ID]=MAX('Table'[Customer ID])&&[Count]>1),[Index])
return
IF(
    [Count]=1||MAX('Table'[Index])=_minindex,1,0)

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

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

    Hi mg00842082 

     

    Can you please repost your sample data. They're being pasted as a single cell for each row.

  • mg00842082's avatar
    mg00842082
    Regular Visitor
    2 years ago

    Customer ID

    Policy Number

    First Name

    Last Name

    Gender

    DOB

    Address 1

    Address 2

    Address 3

    10008001

    45678

    kumar

    Krishna

    M

    12/11/1994

    555

    222

    111

    10008001

    23456

    Krishna

    Gopala

    M

    12/11/1994

    444

    333

    111

    10008001

    87993

    Gopal

    Krish

    M

    12/11/1994

    234

    456

    456

    10008001

    87993

    Gopal

    Krish

    M

    12/11/1994

    234

    456

    456

    10008001

    90080

    Gopal

    Krish

    M

    12/11/1994

    234

    456

    456

    10002001

    11223

    Vinay

    Kumar

    M

    10/8/1995

    3/2/301

    ABC

    123

    10002001

    22334

    rupesh

    sai

    M

    10/8/1995

    2/301

    def

    456

    10004001

    33445

    krishna

    Kumar

    M

    8/7/1995

    4/334

    ghi

    789

    10004001

    43386

    suresh

    Kumar

    M

    8/7/1995

    2/001

    jkl

    901

    10004001

    44556

    suresh

    Kumar

    M

    8/7/1995

    2/001

    jkl

    901

    10005100

    47890

    Ramesh

    sai

    M

    8/8/1996

    DEF

    111

    8/222

    10005100

    87903

    prashanth

    roy

    M

    8/8/1996

    ghi

    888

    9/111

    10008222

    24794

    nikhil

    shetty

    M

    11/11/1992

    GHI

    333

    12月11日

    10008222

    82395

    Baba

    sab

    M

    11/11/1992

    GHI

    444

    11月12日

    10008222

    88772

    Baba

    sab

    M

    11/11/1992

    GHI

    444

    11月12日

    10008222

    99772

    Baba

    sab

    M

    11/11/1992

    GHI

    444

    11月12日

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for the reply from danextian , please allow me to provide another insight: 
    Hi  mg00842082 ,

     

    Here are the steps you can follow：

    1. Add Column – Index Column – From 1.

    2. Create measure.

    Count =
CALCULATE(COUNT('Table'[Customer ID]),ALLEXCEPT('Table','Table'[Customer ID],'Table'[First Name],'Table'[Last Name],'Table'[Address 1],'Table'[Address 2],'Table'[Address 3]))
    Flag =
var _minindex=MAXX(FILTER(ALL('Table'),[Customer ID]=MAX('Table'[Customer ID])&&[Count]>1),[Index])
return
IF(
    [Count]=1||MAX('Table'[Index])=_minindex,1,0)

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly