Forum Discussion
Return consecutive items over a period
Hi,
I'm trying to find a calcualtion that helps return the employees with A and B rating only in the past 2 years. Here's a sample table...
I'd like to return both the employee names as well as the count of employees (2).
Hi， robotica
You can create a filter measure like below and apply it to visual filter pane:
filter = CALCULATE ( COUNT ( 'Table'[Rating] ), FILTER ( ALL ( 'Table' ), 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] ) && 'Table'[Year].[Year] IN { 2022, 2021 } && 'Table'[Rating] IN { "A", "B" } ) )Count_Employee = VAR tab = SUMMARIZE ( 'Table', 'Table'[Employee Name], "_filter1", [filter] ) RETURN COUNTX ( FILTER ( tab, [_filter1] = 2 ), 'Table'[Employee Name] )Best Regards,
Community Support Team _ Eason
3 Replies
- amitchandak
Super User
robotica , Try measure like
Countx(Filter(Summarize(Table, Table[Employee], "_cnt", Countrows(Table), "_cntAB", Countrows(filter(Table, Table[Rating] in {"A" , "B"}) )), [_cnt] =[_cntAB]), [Employee])
amitchandak Thanks, but how do i factor in the time period - 2022 & 2021?
- v-easonf-msft
Community Support
Hi， robotica
You can create a filter measure like below and apply it to visual filter pane:
filter = CALCULATE ( COUNT ( 'Table'[Rating] ), FILTER ( ALL ( 'Table' ), 'Table'[Employee Name] = MAX ( 'Table'[Employee Name] ) && 'Table'[Year].[Year] IN { 2022, 2021 } && 'Table'[Rating] IN { "A", "B" } ) )Count_Employee = VAR tab = SUMMARIZE ( 'Table', 'Table'[Employee Name], "_filter1", [filter] ) RETURN COUNTX ( FILTER ( tab, [_filter1] = 2 ), 'Table'[Employee Name] )Best Regards,
Community Support Team _ Eason
