Forum Discussion

kirwanm1's avatar
kirwanm1
Frequent Visitor
8 years ago
Solved

Distinct Count/Aggregate Ids/Event Logs

Hey everyone,

I have an event log where events are tied by an aggregate_id, and each event has it's own rows

 

Trying to develop a measure that counts the number of aggregate_ids based on row conditions

Scenario 1

Agg_id_1 Offered
Agg_id_2 Offered
Agg_id_2 Accepted
Agg_id_3 Offered
Agg_id_3 Accepted
Agg_id_3 Rejected

Measure 1 -  number of agg_ids that contain just offered,
Measure 2 - Number of agg_ids that have a row with (offered & accepted) & not rejected
Measure 3 - Number of agg_ids that have a offered & accepted & rejected

Not sure where to start... A measure that looks for just offered & accepted and nothing else won't work, since there may be other event log rows there in the future.

Thanks,
Mark.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi kirwanm1,

     

    Based on my test, you should be able to follow steps below to create the three measures your scenario.

     

    1. Add three calculate column to indicate if an aggregate_id contains each status.

     

    has offered = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Offered"
    )
)

    has accepted = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Accepted"
    )
)

    has rejected = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Rejected"
    )
)

     

     

    2. Then you should be able to use the formulas below to create the three measures.

     

    Measure 1 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] <> 1
            && Table1[has rejected] <> 1
    )
)

    Measure 2 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] = 1
            && Table1[has rejected] <> 1
    )
)

    Measure 3 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] = 1
            && Table1[has rejected] = 1
    )
)

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi kirwanm1,

     

    Based on my test, you should be able to follow steps below to create the three measures your scenario.

     

    1. Add three calculate column to indicate if an aggregate_id contains each status.

     

    has offered = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Offered"
    )
)

    has accepted = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Accepted"
    )
)

    has rejected = 
CALCULATE (
    DISTINCTCOUNT ( Table1[status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[aggregate_ids] ),
        Table1[status] = "Rejected"
    )
)

     

     

    2. Then you should be able to use the formulas below to create the three measures.

     

    Measure 1 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] <> 1
            && Table1[has rejected] <> 1
    )
)

    Measure 2 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] = 1
            && Table1[has rejected] <> 1
    )
)

    Measure 3 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            && Table1[has accepted] = 1
            && Table1[has rejected] = 1
    )
)

     

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards