Forum Discussion

keivan's avatar
keivan
Icon for Helper I rankHelper I
3 years ago
Solved

Active connections

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.) Conse...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

keivan's avatar
keivan
Icon for Helper I rankHelper I
3 years ago

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 )

  • keivan's avatar
    keivan
    Icon for Helper I rankHelper I
    3 years ago

    Correct the calculation of expiration date