Forum Discussion
DAX formula
| Cell | Week |
| abc | 35 |
| abc | 34 |
| abc | 33 |
| abc | 32 |
| abc | 31 |
| def | 35 |
| def | 34 |
| def | 32 |
| def | 31 |
| jkl | 34 |
| jkl | 33 |
| jkl | 32 |
| jkl | 31 |
| jkl | 30 |
| pqr | 35 |
| pqr | 34 |
| pqr | 33 |
| pqr | 32 |
| pqr | 31 |
| xyz | 35 |
| xyz | 34 |
| xyz | 33 |
| xyz | 31 |
In My data set i have 2 columns, Cell and week. Consider Week 35 as current week.
Cell abc - Appears in all weeks
Cell def - Appears in all weeks except week 33
Cell jkl - Appears in all weeks except week 35
Cell pqr - Appears in all weeks
Cell xyz - Appears in all weeks except week 32
I want my output which shows me cells which appears continously from current week till last all weeks. if any week is missing then my query end at that point for that cell.
| Cell | Week | reason | |
| abc | 35/34/33/32/31 | Appears in all weeks | |
| def | 35/34 | Appears in all weeks except week 33, so my query should end at week 34 as there is one week gap oberved after that | |
| jkl | since this cell didn't appear in current week, so no output expected | ||
| pqr | 35/34/33/32/31 | Appears in all weeks | |
| xyz | 35/34/33 | Appears in all weeks except week 32, so my query should end at week 33 as there is one week gap oberved after that |
Pls help, how can i get this week info in one columns against each cell.
in conclusion i want all the different weeks starting current week against each cell as output and if any cell observe any week break in between then the result for that cell should be appear accordingly.
4 Replies
prashantg364 Here is one way, PBIX attached below signature.
Measure 2 = VAR __MinWeek = MINX( ALL('Table'[Week]), [Week] ) VAR __MaxWeek = MAXX( ALL('Table'[Week]), [Week] ) VAR __MaxWeekCell = MAX( 'Table'[Week] ) VAR __Count = COUNTROWS( DISTINCT( 'Table'[Week] ) ) VAR __Path = CONCATENATEX( DISTINCT( 'Table'[Week] ), [Week], "|", [Week], DESC ) VAR __Table = ADDCOLUMNS( GENERATESERIES( 1, __Count, 1 ), "__Week", PATHITEM( __Path, [Value] ) + 0 ) VAR __Table2 = ADDCOLUMNS( __Table, "__Gap", VAR __CurWeek = [__Week] VAR __Next = MAXX( FILTER( __Table, [__Week] < __CurWeek ), [__Week] ) VAR __Result = IF( __CurWeek = __MaxWeekCell, 1, __CurWeek - __Next ) RETURN __Result ) VAR __GapWeek = MAXX( FILTER( __Table2, [__Gap] > 1 ), [__Week] ) VAR __GapWeekFinal = SWITCH( TRUE(), __MaxWeekCell <> __MaxWeek, BLANK(), __GapWeek = BLANK(), __MinWeek, __GapWeek ) VAR __Result = IF( __GapWeekFinal = BLANK(), BLANK(), CONCATENATEX( FILTER( DISTINCT( 'Table'[Week] ), [Week] >= __GapWeek ), [Week], "/", [Week], DESC ) ) RETURN __ResultCELL Measure Measure 2 Output output expected ATG7130Z_7NB03_S04 33 33 correct ATG8512Z_7NB03_S04 33/28 33/28 not correct 33 ATG8524Z_7NB03_S02 33/26 33/26 not correct 33 ATG8527Z_7NB03_S01 33 33 correct ATGC004Z_7NB03_S03 33/28/27/26/25/24 33/28/27/26/25/24 not correct 33 AYT0013H_7NB01_S02 33/32/31/28/27/24 33/32/31 correct AYT0020H_7NB01_S01 33/32/31/30/29/28/27/26/25/24 33/32/31/30/29/28/27/26/25/24 correct
Hi Greg_Deckler
Thanks for the help. Majority of the cases i am getting correct output, but still some cell is giving wrong output as shown in the table above.
Kindly re-look and help!!prashantg364 You'll have to provide a more comprehensive set of sample data then so I can find the edge cases where things are going wrong.
Hi prashantg364 , hello Greg_Deckler, thank you for your prompt reply!
We are looking forward to your feedback if there is any update.
Please remember to update this thread if you have any progress.
Thank you for your understanding.
