Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to compare two strings in consecutive rows and display result in new column

Hi All,

 

I am looking for the solution for a query where in I have created a calculated columns in Power BI. 

 

We need to calculate “total shipment count” in Power BI. For calculating total shipment count we need to apply two conditions:

 

  1. If the “Plant_Shpto_Shpment_Gross KG” is 0 then shipment count will be 0.
  2. Secondly, we have to compare the consecutive rows of the “Plnt_Ship-to_Shpmt_Mat” column. Please refer the snapshot below to view the formula(need to use similar kind formula in DAX) used to get the desired shipment count in Excel. If the values in the consecutive rows are same, it should return 0 as shipment count.Excel Formula

     

  3.  I have used following formula in power BI but its showing error.DAX formula

     

  4. I also tried changing the data type for Shipment number but due to "0101_150190142_DRX0062220_1720.91" type of values it's giving error as "Cannot convert value '0101_150190142_DRX0062220_1720.91' of type Text to type Integer".

      

     

    Can someone please help. 

    Thanks 

Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    in my syntax the TRUE result was the same shipment, FALSE was 0, 

    RETURN
    IF ( 'Table'[Plant_Shpto_Shpment_Gross KG] <> 0, IF ( SameShipment, 0, 1 ), 0 )

    In your syntax it's reverse, so you either need to change '<>' to '=' to fully reverse the logic, or switch 0 and same shipment condition

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

    I believe you can use 

     

    VALUES or MIN or MAX or SELECTEDVALUE

     

    instead of

     

    SUM

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    try this

    Column = 
VAR CurrentIndex = 'Table'[Index]
VAR PreviousShipment =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Plnt_Ship-to_Shpmt_Mat], TRUE ),
        FILTER ( 'Table', 'Table'[Index] = CurrentIndex - 1 )
    )
VAR SameShipment = 'Table'[Plnt_Ship-to_Shpmt_Mat] = PreviousShipment
RETURN
    IF ( 'Table'[Plant_Shpto_Shpment_Gross KG] <> 0, IF ( SameShipment, 0, 1 ), 0 )
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Stachu

       

      Hi, I tried doing this but "'Table'[Plant_Shpto_Shpment_Gross KG] <> 0" condition is not validating properly.  I have attached the snapshot below:

       

       

      can you please help if i am missing something.

       

      Thanks

       

      • Stachu's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        8 years ago

        in my syntax the TRUE result was the same shipment, FALSE was 0, 

        RETURN
    IF ( 'Table'[Plant_Shpto_Shpment_Gross KG] <> 0, IF ( SameShipment, 0, 1 ), 0 )

        In your syntax it's reverse, so you either need to change '<>' to '=' to fully reverse the logic, or switch 0 and same shipment condition