Forum Discussion
Daretoexplore
2 years agoAdvocate I
Identifying relevant rows
Hi all My dataset records everything, and I am struggling to create something that helps sort it. My dataset is as follows: Cust ID. Status. Date (UK) CST001 E...
- 2 years ago
Daretoexplore Might be best to have an index column, but you could do it like this:
Column = VAR __Cust = [Cust ID.] VAR __MaxDate = MAXX( FILTER( 'Table', [Cust ID.] = __Cust && [Status.] = "Entered Shop" ), [Date (UK)]) VAR __Result = IF( [Date (UK)] = __MaxDate, 1, 0 ) RETURN __ResultThen for the measure:
Measure = VAR __Cust = MAX('Table'[Cust ID.]) VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result - 2 years ago
Daretoexplore Likely need to use >= in the column formula when comparing to __MaxDate instead of =.
Greg_Deckler
2 years agoCommunity Champion
Daretoexplore Might be best to have an index column, but you could do it like this:
Column =
VAR __Cust = [Cust ID.]
VAR __MaxDate = MAXX( FILTER( 'Table', [Cust ID.] = __Cust && [Status.] = "Entered Shop" ), [Date (UK)])
VAR __Result = IF( [Date (UK)] = __MaxDate, 1, 0 )
RETURN
__Result
Then for the measure:
Measure =
VAR __Cust = MAX('Table'[Cust ID.])
VAR __Table = SUMMARIZE( FILTER('Table', [Column] = 0), [Date (UK)] )
VAR __Result = COUNTROWS( __Table )
RETURN
__Result