Need help on Sequence number in same column and Horizontal records in same row

[Vinay07]

Hi All,

Please find the below screenshot of sample data, need help on the "Horizontal records" in a same row and "Sequence number" for different Insureds numbers.

Sample Data:

Below are the requirements need to work on the DAX calculated column: "Upload ID", "Policyholder Name" and "InsuredS". Please help.

Requirement:

1. Under "UploadId" and "Policyholder Name" In case multiple values are present for a single Application Number, list all items need to display horizontally, separated by a space. Maximum of 20 characters allowed, in case this limit will be exceeded display "..." at the end

2. For "Insured Branch Number" new calculated column display a sequence of numbers starting from "1" in case multiple insureds are present within the policy

Expected OUTPUT: Here is the OUTPUT need to display in Power BI with the help of sample data. Please help.

Sample Data Reocrds: Here is the sample records can work for this expected OUTPUT. 

Policy Product Type Application Number Image Upload Date and Time Upload ID Policyholder Client ID Policyholder Name InsuredS
9000123 GH 30330330311 2024/7/03 13:00:02 bheemvi 7185244885 bheemvi 8541224151
1236121 RT 77879174306 2024/7/03 13:00:02 surfati 5535470245 surfati 7563424173
3954848 MM 40760081519 2024/7/03 13:29:30 kudou 7128024728 kudou 5535470245
3954848 MM 40760081519 2024/7/03 13:29:30 raju 7128024728 raju 9641540351
3954848 MM 40760081519 2024/7/03 13:29:30 venky 7128024728 venky 8541224151
6864730 CN 40510010297 2024/7/03 14:02:99 suresh 8541224151 suresh 7563424173
9000520 BO 40760081311 2024/7/03 14:02:99 naresh 7563424173 naresh 5535470245
1236121 RT 77879174306 2024/7/03 15:00:02 gopal 5535470245 surfati 9873424173

[Anonymous · Accepted solution]

Hi @Vinay07 ,

Thanks SachinNandanwar  for the quick reply and solution. I noticed one more point that needs to be added:

We can create two columns.

Upload_Id = 
var _id=CONCATENATEX (
    FILTER (
        'Table',
        'Table'[Policyholder Client ID] = EARLIER ( 'Table'[Policyholder Client ID] )
    ),
    'Table'[Upload ID],
    " ",
    'Table'[Image Upload Date and Time], DESC
)
var _len=LEN(_id)
RETURN IF(_len>20,_id & "...",_id)

Insured Branch No = 

RANKX (
    FILTER (
        'Table',
       'Table'[Policyholder Client ID] = EARLIER ( 'Table'[Policyholder Client ID])
    ),
    'Table'[InsuredS],
    , 
    ASC,Dense
)

Best Regards,

Neeko Tang

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

[SachinNandanwar]

Create two calculated columns.

Upload Id : 

Upload_Id = 
CONCATENATEX (
    FILTER (
        '1',
        '1'[ Policyholder Client ID] = EARLIER ( '1'[ Policyholder Client ID] )
    ),
    '1'[ Upload ID],
    ",",
    '1'[ Image Upload Date and Time], DESC
)

Insured Branch Number

Insured Branch No = 

RANKX (
    FILTER (
        '1',
       '1'[ Policyholder Client ID] = EARLIER ( '1'[ Policyholder Client ID])
    ),
    '1'[ InsuredS],
    , 
    ASC,Dense
)