Distinct Count/Aggregate Ids/Event Logs

[kirwanm1]

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.

[v-ljerr-msft · Accepted solution]

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. 

Regards