Forum Discussion

Harish85's avatar
Harish85
Helper III
3 months ago
Solved

select only one row from the column

In the above report we should get only two rows for Object_ID ,it is sample example , I have report with large data , so I should select only each column one ship to and one sold to. Partner_FC...
Need Help
  • techies's avatar
    techies
    3 months ago

    Hi Harish85  As per my understanding, all 3 of your threads point to the same requirement as creating 2 separate columns (SHIP_TO and SOLD_TO) from one column based on OBJECT_ID.If you prefer not to use Power Query or CONCATENATEX, please try this calculated table:

     

    Complaint Partners =
    DISTINCT (
        SELECTCOLUMNS (
            VALUES ( 'SampleFile'[OBJECT_ID] ),
            "OBJECT",    'SampleFile'[OBJECT_ID],
            "SHIP_TO",   CALCULATE (
                             FIRSTNONBLANK ( 'SampleFile'[PARTNER], 1 ),
                             'SampleFile'[PARTNER_DESC] = "SHIP_TO"
                         ),
            "SOLD_TO",   CALCULATE (
                             FIRSTNONBLANK ( 'SampleFile'[PARTNER], 1 ),
                             'SampleFile'[PARTNER_DESC] = "SOLD_TO"
                         )
        )
    )
     
     
     

     

techies's avatar
techies
Super User
3 months ago

Hi Harish85  As per my understanding, all 3 of your threads point to the same requirement as creating 2 separate columns (SHIP_TO and SOLD_TO) from one column based on OBJECT_ID.If you prefer not to use Power Query or CONCATENATEX, please try this calculated table:

 

Complaint Partners =
DISTINCT (
    SELECTCOLUMNS (
        VALUES ( 'SampleFile'[OBJECT_ID] ),
        "OBJECT",    'SampleFile'[OBJECT_ID],
        "SHIP_TO",   CALCULATE (
                         FIRSTNONBLANK ( 'SampleFile'[PARTNER], 1 ),
                         'SampleFile'[PARTNER_DESC] = "SHIP_TO"
                     ),
        "SOLD_TO",   CALCULATE (
                         FIRSTNONBLANK ( 'SampleFile'[PARTNER], 1 ),
                         'SampleFile'[PARTNER_DESC] = "SOLD_TO"
                     )
    )
)