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_FCT 001 and 002.

 

Please help me.

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"
                         )
        )
    )
     
     
     

     

5 Replies

  • 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.

     

     



  • techies's avatar
    techies
    Icon for Super User rankSuper 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"
                         )
        )
    )
     
     
     

     

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 months ago

    Hi Harish85 

    The reason why there are four rows is because there are  multiple SOLDTO_SHIPTO distinct values for the same PROD_DESCRITION. The question is which one do you want to return - the sold to or ship to rows? What result do you expect? Which rows should be removed and why?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 months ago

    Hi Harish85,

    I would also take a moment to thank danextian  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

    Regards,
    Community Support Team.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 months ago

      Hi Harish85,
      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


      Regards,
      Community Support Team.