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 co...
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

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