Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
51 | |
38 | |
20 | |
14 | |
13 |
User | Count |
---|---|
98 | |
71 | |
29 | |
20 | |
13 |