Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
85 | |
78 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |