Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Not applicable

aggregate group by max date - recalculate based on filters

Hi, Fellow power bi users,

1aaa1June 15, 2020May 31, 2020
1aaa2May 16, 2020May 31, 2020
2aaa1May 15, 2020May 31, 2020
2aaa2May 16, 2020May 31, 2020
1bbb1May 15, 2020May 31, 2020
1bbb2May 16, 2020May 31, 2020
2bbb1May 15, 2020May 31, 2020
2bbb2May 16, 2020May 31, 2020
3xxx1May 15, 2021May 31, 2021
3xxx2May 16, 2021May 31, 2021
4xxx1May 15, 2021May 31, 2021
4xxx2May 16, 2021May 31, 2021
3yyy1May 15, 2021May 31, 2021
3yyy2May 16, 2021May 31, 2021
4yyy1May 15, 2021May 31, 2021
4yyy3May 16, 2021May 31, 2021


In the above table, I want to calculate count(unique(ID)) per timestamp where max(next_billing_date) < time_stamp.  



Output 1: 

Filters: when all filters are selected

time_stamp.        total_ID_count
May 31, 20201
May 31, 20214



Output 2:


when quantity 3 is selected

all products are selected

time_stamp.      total_ID_count
May 31, 20200
May 31, 20211


Output 3:


when product aaa is selected

and all quantity is selected

time_stamp.        total_ID_count
May 31, 20201
May 31, 20210



Can you please give some pointers on how to approach this? Should I just handle it in the backend using python?


Update 1:


Looking for Group by ID, Product_name, Quantity.

If all the products are selected in the product_name then group by ID and selected Quantities. 

Similarly, If Quantities are selected in the Quantity filter then group by ID and selected Product_names. 

If all Products_name and Quantity are checked (Select All), then Just group by ID and give unique ID count. 


Update 2:

Can't create calculated column as it won't change with filters or slicers

Super User
Super User

@Anonymous , have you tried a measure like this with timestamp


calculate(distinctCOUNT(Table[ID]), filter(Table, Table[next_billing_date] < Table[time_stamp]))

Not applicable

@amitchandak Thank you so much for the reply. Sorry, I forgot to mention one of the conditions. I updated the question. The constraint is max(next_billing_date) < time_stamp. I need to group first and then see if all the next_billing_date within the group is < time_stamp. If so then add it to count. 

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.