Forum Discussion

Harish85's avatar
Harish85
Icon for Helper III rankHelper 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"
                         )
        )
    )
     
     
     

     

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
3 months ago

Based on the example you provided, it looks like the 'PROD_DESCRIPTION' column has multiple values for a single OBJECT_ID, PARTNER_FCT combination. This is why multiple rows are returning. One possible solution is to write a CONCATENATEX measure to combine all of the rows of PROD_DESCRIPTION into as single row. 
Before

After

Measure...

Description = 
CONCATENATEX('Table', [PROD_DESCRIPTION], ", ")

Hope this gets you pointed in the right direction.