Forum Discussion
Helper IReconnection rate -
Community Championkeivan Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Helper IThanks Greg,
I checked but I did not find this specific topin on most popular questions.
I would always add table in future.
here more info:
date are in format yyyy-mm-dd hh:mm:ss.000
| consent_business_id | connection_business_id | consent_status | consent_creation_date | consent_creation_date_1 | consent_valid_until | consent_ok | consent_new | consent_fail |
| 11 | 2 | ok | 2022-09-05 12:00:00.000 | 2022-09-05 | 2022-12-04 12:00:00.000 | 1 | 0 | 0 |
| 15 | 2 | ok | 2022-12-06 05:54:20.000 | 2022-12-06 | 2023-03-06 05:54:20.000 | 1 | 0 | 0 |
| 12 | 69 | ok | 2022-08-06 08:42:15.000 | 2022-12-06 | 2022-11-04 08:42:15.000 | 1 | 0 | 0 |
| 16 | 69 | new | 2022-08-09 08:42:15.000 | 0 | 1 | 0 | ||
| 17 | 77 | fail | 2022-12-06 05:53:16.000 | 0 | 0 | 1 |
as you may see for connection_business_id = 2 a connection was created on 5th Sept. it expired after 90 days (consent_id = 11). Then it was renew (consent_id = 15).
connection_id = 69 after the first consent = 12 expired, an attempt of renewal begun but it fail. consent_status = fail.
What I want to get is a KPI, reporting the % of how many conenctions are renew. So I want to take into consideration only consent ok and expired by at least 14 days (to give people time to renew).
where consent_status = 'ok' and consen_valid_until < TODAY() -14
Then in this specific case
consent_renewal % = 2/2 = 100%. Out of the 5 consents only 3 are ok and only 2 are expired by more than 14 days (consent_id 11 and 12)
consent_renewal_success % = 1/2 = 50% Out of 2 renewal only 1 is with consent_status_ok (consent_id = 15)
Pleas elet me know if it is clear or still confusing the question.
Thanks,
- keivan
Hi Greg_Deckler
did you had chance to take a look?