Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 FormulaExcel Formula

     

  3.  I have used following formula in power BI but its showing error.DAX formulaDAX 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 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

5 REPLIES 5
Stachu
Community Champion
Community Champion

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 )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

@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:TrialMoshps.png

 

 

can you please help if i am missing something.

 

Thanks

 

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

ohh I missed that part.

I corrected it. Its giving correct output now.. Thanks a lot....Smiley Very Happy

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

I believe you can use 

 

VALUES or MIN or MAX or SELECTEDVALUE

 

instead of

 

SUM


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.