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 all,
I would like to calculate the number of active connections.
My dataset is compose in this way:
Calendar Table (Date, Year, Month, etc.)
Connections Table (connection_id, etc.)
Consents Table (consent_id, connection_id, valid_until, etc.)
When a client create a connection (connection_id 1111) he provide a consent (consent_id 2222) this consent has an expiration date (for example 25/08/2022). When this consent expire the client can renew the consent for its connection.
If the client renew the consent, a new consent_id will be generate (consent_id 3333) but the connection_id would NOT change (connection_id 1111).
In order to calculate if an active connection is active, I have to check if the valid_until of its newest consent is < TODAY or not.
Consent_id are integer and are progressive numbers.
Sorry to bother you, but I don't know how to make BI perform this calculation.
Thanks,
Solved! Go to Solution.
Hi @keivan ,
If you want to get active connection id, you can create a measure to filter your id list.
Measure =
VAR _LASTDATE =
CALCULATE (
MAX ( Consents[valid_until] ),
FILTER (
Consents,
Consents[connection_id] = MAX ( Connections[connection_id] )
)
)
RETURN
IF ( _LASTDATE >= TODAY (), 1, 0 )
Add this measure into visual level filter and set it to show items when value =1.
Result is as below.
According to your statement, I think your requirement is based on your data model. Please share a sample file with me and show me a screenshot with the result you want.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @keivan ,
If you want to get active connection id, you can create a measure to filter your id list.
Measure =
VAR _LASTDATE =
CALCULATE (
MAX ( Consents[valid_until] ),
FILTER (
Consents,
Consents[connection_id] = MAX ( Connections[connection_id] )
)
)
RETURN
IF ( _LASTDATE >= TODAY (), 1, 0 )
Add this measure into visual level filter and set it to show items when value =1.
Result is as below.
According to your statement, I think your requirement is based on your data model. Please share a sample file with me and show me a screenshot with the result you want.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Rico,
It almost works.
I paste below the formula. If I remove the Return to the formula calculates correctly among the various consent the last one and report the expiration date. ( I share the screenshot).
However keeping
RETURN
IF ( _LASTDATE >= TODAY (), 1, 0 )
It reurns always 0
Active Connections =
VAR _LASTDATE =
CALCULATE (
MAX (consents[consent_valid_until] ),
FILTER (
Consents,
consents[connection_business_id] = MAX ( Connections[connection_business_id] )
)
)
RETURN
IF ( _LASTDATE >= TODAY (), 1, 0 )
Correct the calculation of expiration date
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |