Distinct Count/Aggregate Ids/Event Logs

Question

Hey everyone,I have an event log where events are tied by an aggregate_id, and each event has it's own rows&nbsp;Trying to develop a measure that counts the&nbsp;number of aggregate_ids based on row conditionsScenario 1Agg_id_1 OfferedAgg_id_2 OfferedAgg_id_2 AcceptedAgg_id_3 OfferedAgg_id_3 AcceptedAgg_id_3 RejectedMeasure 1 -&nbsp; number of agg_ids that contain just offered,Measure 2 - Number of agg_ids that have a row with (offered &amp; accepted) &amp; not rejectedMeasure 3 - Number of agg_ids that have a offered &amp; accepted &amp; rejectedNot sure where to start... A measure that looks for just offered &amp; 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 Answer

Hi&nbsp;kirwanm1,
&nbsp;
Based on my test, you should be able to follow steps below to create the three measures your scenario.
&nbsp;
1. Add three calculate&nbsp;column to indicate if an&nbsp;aggregate_id contains each status.
&nbsp;
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"
    )
)

&nbsp;
&nbsp;
2. Then you should be able to use the formulas below to create the three measures.
&nbsp;
Measure 1 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            &amp;&amp; Table1[has accepted] &lt;&gt; 1
            &amp;&amp; Table1[has rejected] &lt;&gt; 1
    )
)

Measure 2 = 
CALCULATE (
    DISTINCTCOUNT ( Table1[aggregate_ids] ),
    FILTER (
        Table1,
        Table1[has offered] = 1
            &amp;&amp; Table1[has accepted] = 1
            &amp;&amp; Table1[has rejected] &lt;&gt; 1
    )
)

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

&nbsp;
&nbsp;
Here is the sample pbix file for your reference.&nbsp;:smileyhappy:
&nbsp;
Regards

Forum Discussion

Related content

distinct count aggregate

Aggregating conditional distinct counts

Exclude items from distinct count on higher level of aggregation

Aggregating Distinct Count measure and Aggregating IF measure

Aggregation of Distinct Count Measure