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, Fellow power bi users,
ID | product_name | quantity | next_billing_date | time_stamp |
1 | aaa | 1 | June 15, 2020 | May 31, 2020 |
1 | aaa | 2 | May 16, 2020 | May 31, 2020 |
2 | aaa | 1 | May 15, 2020 | May 31, 2020 |
2 | aaa | 2 | May 16, 2020 | May 31, 2020 |
1 | bbb | 1 | May 15, 2020 | May 31, 2020 |
1 | bbb | 2 | May 16, 2020 | May 31, 2020 |
2 | bbb | 1 | May 15, 2020 | May 31, 2020 |
2 | bbb | 2 | May 16, 2020 | May 31, 2020 |
3 | xxx | 1 | May 15, 2021 | May 31, 2021 |
3 | xxx | 2 | May 16, 2021 | May 31, 2021 |
4 | xxx | 1 | May 15, 2021 | May 31, 2021 |
4 | xxx | 2 | May 16, 2021 | May 31, 2021 |
3 | yyy | 1 | May 15, 2021 | May 31, 2021 |
3 | yyy | 2 | May 16, 2021 | May 31, 2021 |
4 | yyy | 1 | May 15, 2021 | May 31, 2021 |
4 | yyy | 3 | May 16, 2021 | May 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, 2020 | 1 |
May 31, 2021 | 4 |
Output 2:
Filters:
when quantity 3 is selected
all products are selected
time_stamp. | total_ID_count |
May 31, 2020 | 0 |
May 31, 2021 | 1 |
Output 3:
Filters:
when product aaa is selected
and all quantity is selected
time_stamp. | total_ID_count |
May 31, 2020 | 1 |
May 31, 2021 | 0 |
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
@Anonymous , have you tried a measure like this with timestamp
calculate(distinctCOUNT(Table[ID]), filter(Table, Table[next_billing_date] < Table[time_stamp]))
@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.
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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |