Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Creating a new value based on previous row

‎10-12-2018 11:14 AM

Hello,

 

Trying to figure this one out.  I have this working in Excel, but want to migrate to using an active SQL Server connection.  

 

In the Excel version, I have the Calculated value Weeks Out Sales Meeting created using the values in Meeting Week 0 and JoinCol. Here is a sample set of data:

 

 ABCD
 JoinColTransWeekMeeting Week 0Weeks Out Sales Meeting
1002150:WARREN NELSON:NICK AGEE:FRANK MACK1/21/2018  
2002150:WARREN NELSON:NICK AGEE:FRANK MACK1/28/2018  
3002150:WARREN NELSON:NICK AGEE:FRANK MACK2/4/2018  
4002150:WARREN NELSON:NICK AGEE:FRANK MACK2/11/201800
5002150:WARREN NELSON:NICK AGEE:FRANK MACK2/18/2018 1
6002150:WARREN NELSON:NICK AGEE:FRANK MACK2/25/2018 2
7002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/21/2018  
8002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/28/2018  
9002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/4/2018  
10002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/11/201800
11002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/18/2018 1
12002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/25/2018 2

 

The Column Weeks Out Sales meeting is populated with the following formula in Excel:

 

=IFERROR(IF(D3=0,0,IF(B3=B2,E2+1,"")),"")

 

What is the best way to replicate this formula using DAX in PBI?

Labels:
Message 1 of 7
1,490 Views
0
1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

‎10-14-2018 08:07 PM

@Anonymous,

You can create the following columns in your table.

Rank = RANKX(FILTER(Table1,Table1[JoinCol]=EARLIER(Table1[JoinCol])),Table1[TransWeek],,ASC,Dense)

Column = CALCULATE(FIRSTNONBLANK(Table1[Rank],1),NOT(ISBLANK(Table1[Meeting Week 0])),ALLEXCEPT(Table1,Table1[JoinCol]))
Weeks Out Sales Meeting = var tempvalue=Table1[Rank]-Table1[Column] return IF(tempvalue>=0,tempvalue,BLANK())

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 2 of 7
1,471 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎10-12-2018 10:11 AM

I am working on moving a data set from Excel to a live SQL Server connection.  In the Excel version, I have the Calculated value Weeks Out Sales Meeting created using the values in Meeting Week 0 and JoinCol. Here is a sample set of data:

 

 ABCD
 JoinColTransWeekMeeting Week 0Weeks Out Sales Meeting
1002150:WARREN NELSON:NICK AGEE:FRANK MACK1/21/2018  
2002150:WARREN NELSON:NICK AGEE:FRANK MACK1/28/2018  
3002150:WARREN NELSON:NICK AGEE:FRANK MACK2/4/2018  
4002150:WARREN NELSON:NICK AGEE:FRANK MACK2/11/201800
5002150:WARREN NELSON:NICK AGEE:FRANK MACK2/18/2018 1
6002150:WARREN NELSON:NICK AGEE:FRANK MACK2/25/2018 2
7002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/21/2018  
8002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/28/2018  
9002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/4/2018  
10002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/11/201800
11002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/18/2018 1
12002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/25/2018 

2

 


The Column Weeks Out Sales meeting is populated with the following formula in Excel:

=IFERROR(IF(D3=0,0,IF(B3=B2,E2+1,"")),"")

What is the best way to replicate this formula using DAX in PBI?

Message 7 of 7
1,476 Views
0
Anonymous
Not applicable

‎10-12-2018 11:04 AM

I am working on moving a data set from Excel to a live SQL Server connection. In the Excel version, I have the Calculated value Weeks Out Sales Meeting created using the values in Meeting Week 0 and JoinCol. Here is a sample set of data:

 

 ABCD
 JoinColTransWeekMeeting Week 0Weeks Out Sales Meeting
1002150:WARREN NELSON:NICK AGEE:FRANK MACK1/21/2018  
2002150:WARREN NELSON:NICK AGEE:FRANK MACK1/28/2018  
3002150:WARREN NELSON:NICK AGEE:FRANK MACK2/4/2018  
4002150:WARREN NELSON:NICK AGEE:FRANK MACK2/11/201800
5002150:WARREN NELSON:NICK AGEE:FRANK MACK2/18/2018 1
6002150:WARREN NELSON:NICK AGEE:FRANK MACK2/25/2018 2
7002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/21/2018  
8002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/28/2018  
9002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/4/2018  
10002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/11/201800
11002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/18/2018 1
12002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/25/2018 

2

 

The Column Weeks Out Sales meeting is populated with the following formula in Excel:

 

=IFERROR(IF(D3=0,0,IF(B3=B2,E2+1,"")),"")

 

What is the best way to replicate this formula using DAX in PBI?

Message 6 of 7
1,464 Views
0
v-yuezhe-msft
Employee
Employee

‎10-14-2018 08:07 PM

@Anonymous,

You can create the following columns in your table.

Rank = RANKX(FILTER(Table1,Table1[JoinCol]=EARLIER(Table1[JoinCol])),Table1[TransWeek],,ASC,Dense)

Column = CALCULATE(FIRSTNONBLANK(Table1[Rank],1),NOT(ISBLANK(Table1[Meeting Week 0])),ALLEXCEPT(Table1,Table1[JoinCol]))
Weeks Out Sales Meeting = var tempvalue=Table1[Rank]-Table1[Column] return IF(tempvalue>=0,tempvalue,BLANK())

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 7
1,472 Views
0
Anonymous
Not applicable
In response to v-yuezhe-msft

‎10-18-2018 06:21 AM

@v-yuezhe-msft

 

Follow up to this.  What changes need to be made to reset the Row/Column counters when a second Week 0 Meeting occurs for the same Join Col value?  I've been trying a few things, but my limited knowledge of PBI & DAX are hindering my efforts.

Message 4 of 7
1,444 Views
0
v-yuezhe-msft
Employee
Employee
In response to Anonymous

‎10-23-2018 01:46 AM

@Anonymous,

Please open a new thread with sample data and expected result.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 5 of 7
1,427 Views
0
Anonymous
Not applicable
In response to v-yuezhe-msft

‎10-16-2018 06:12 PM

Thank you, Lydia.  That solution works perfectly.

 

The only item I found I had to do was save the workbook before calculating out the last item.

Message 3 of 7
1,451 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

View All