Forum Discussion
prashantg364
Helper II
2 years agoDAX 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 3...
Greg_Deckler
Community Champion
2 years agoprashantg364 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
__Resultprashantg364
Helper II
2 years ago| CELL | 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!!