Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm trying to get count of top 1 records which are active , filtered by date range for each group. For example, if filtered by 31/07/2018 , measure should count active top 1 records
Row_Number | Group | Event_Date | IsActive |
1 | A | 1/08/2018 | 1 |
2 | A | 2/06/2018 | 1 |
3 | A | 25/05/2018 | 0 |
4 | A | 4/04/2018 | 0 |
5 | A | 13/03/2018 | 0 |
1 | B | 6/08/2018 | 1 |
2 | B | 17/03/2018 | 0 |
3 | B | 28/02/2018 | 1 |
4 | B | 9/02/2018 | 1 |
1 | C | 15/08/2018 | 1 |
2 | C | 12/08/2018 | 1 |
3 | C | 17/07/2018 | 1 |
Also this measure needs to be reported by Month as well, so needs to get the all values before what ever the ending date. This is what I came up with, but when filtered by month it excludes the last month events
= CALCULATE ( COUNT ( 'Table'[Row_Number] ), FILTER ( ADDCOLUMNS ( 'Table', "MaxRecord", CALCULATE ( MAX ( 'Table'[Row_Number] ), FILTER ( 'Table', 'Table'[Group] = EARLIER ( 'Table'[Group] ) ), FILTER ( ALL ( 'Table'[Event_Date] ), 'Table'[Event_Date] <= MAX ( 'Date'[DateId] ) ), ALL ( 'Date' ) ) ), [MaxRecord] = 'Table'[Row_Number] && 'Table'[IsActive] = 1 ) )
Any help would be appreciated.
So, what makes a record a "top" record?
Hi @Anonymous,
You can try to use below measure to calculate active row count of filtered max row of each group:
Max Actived Row count = VAR summary = ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Table1 ), [Group], "MaxRow", MAX ( Table1[Row_Number] ) ), "IsActive", LOOKUPVALUE ( Table1[IsActive], Table1[Group], [Group], Table1[Row_Number], [MaxRow] ) ) RETURN COUNTROWS ( FILTER ( summary, [IsActive] = 1 ) )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |