Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |