Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
A | B | C | D | |
JoinCol | TransWeek | Meeting Week 0 | Weeks Out Sales Meeting | |
1 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/21/2018 | ||
2 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/28/2018 | ||
3 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/4/2018 | ||
4 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/11/2018 | 0 | 0 |
5 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/18/2018 | 1 | |
6 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/25/2018 | 2 | |
7 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/21/2018 | ||
8 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/28/2018 | ||
9 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/4/2018 | ||
10 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/11/2018 | 0 | 0 |
11 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/18/2018 | 1 | |
12 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/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?
Solved! Go to Solution.
@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())
Regards,
Lydia
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:
A | B | C | D | |
JoinCol | TransWeek | Meeting Week 0 | Weeks Out Sales Meeting | |
1 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/21/2018 | ||
2 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/28/2018 | ||
3 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/4/2018 | ||
4 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/11/2018 | 0 | 0 |
5 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/18/2018 | 1 | |
6 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/25/2018 | 2 | |
7 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/21/2018 | ||
8 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/28/2018 | ||
9 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/4/2018 | ||
10 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/11/2018 | 0 | 0 |
11 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/18/2018 | 1 | |
12 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/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?
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:
A | B | C | D | |
JoinCol | TransWeek | Meeting Week 0 | Weeks Out Sales Meeting | |
1 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/21/2018 | ||
2 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 1/28/2018 | ||
3 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/4/2018 | ||
4 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/11/2018 | 0 | 0 |
5 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/18/2018 | 1 | |
6 | 002150:WARREN NELSON:NICK AGEE:FRANK MACK | 2/25/2018 | 2 | |
7 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/21/2018 | ||
8 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 1/28/2018 | ||
9 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/4/2018 | ||
10 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/11/2018 | 0 | 0 |
11 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/18/2018 | 1 | |
12 | 002150:WARREN NELSON:NICK AGEE:JIM DAVIS | 2/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?
@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())
Regards,
Lydia
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.
@Anonymous,
Please open a new thread with sample data and expected result.
Regards,
Lydia
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.
User | Count |
---|---|
90 | |
84 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |