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 Reference previous row in same column

I have one column like this

 

City

A

A

B

C

C

 

and my requirement is if previous(city)=city then 0 else city and first one should be  0

 

So output

 

city     Column

A         0

A         0 

B         B

C         C

C         0

8 REPLIES 8
Anonymous
Not applicable

 I have below SSRS logic

 IIF(Previous(Fields!name.Value) = Fields!name.Value,"",Fields!name.Value )

 

I want to implement it in Power BI.

Note: My source is Azure Analysis Services so I can not create index column

 

e.g

Name  output

A       

A        

B            B

C            C

Anonymous
Not applicable

Output = IF(CALCULATE(COUNT(Table1[Source]),FILTER(Table1,Table1[Source]=EARLIER(Table1[Source])))>1,"",Table1[Source])

Anonymous
Not applicable

I want measure not calculated column as my source is AAS. it won't allow creation of new calculated column

PattemManohar
Community Champion
Community Champion

@Anonymous  Please add an Index column in "Power Query Editor" and then add a new column in Data Pane as below

 

CheckPrev = 
VAR _PrevRow = LOOKUPVALUE(Test242PrevRow[City],Test242PrevRow[Index],Test242PrevRow[Index]-1)
RETURN IF(Test242PrevRow[City]=_PrevRow || Test242PrevRow[Index] = 1,"0",Test242PrevRow[City])

image.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

and in real time scenario i don't know how many rows are there so please provide dynamic solution

Anonymous
Not applicable

Is it possible without adding column?

@Anonymous  Don't think it's possible without having any supporting field to lookup on to retrieve next or previous row logic. 

 

It's a dynamic solution and it will work irrespective of number of rows in your table....





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

AS my data source is AAS so it won't allow me to create column and it only allow me to create measure

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.