cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Count of first occurrence of an event per group WITH A USER FILTER

Hi all, I'am struggling with calculation so it takes into account a user filter (for example slicer) on source data.

 

Basically, I am trying to create a measure which represents first-click campaign attribution weight. In data language, it means to count campaigns which occured first per opportunity. The first is the campaign with highest number of days to conversion.


I have data like this:

image.png

 

I have created following two measures and they work okay until user filters data 😕 . I'd like to have  the source data filtered based on the slicers, not the results. I tried to replace ALL with ALLSELECTED and many more tweaks but it doesn't work.

 

See screenshot or attached Sample PBIX workbook, there are sample data and expected result.

 

Thanks for any help 🙂

 

Is first occurence of campaign per opportunity = 
IF (
    SUM ( attribution[days] )
        = CALCULATE (
            MAX ( attribution[days] ),
            ALL ( attribution ),
            VALUES ( attribution[opportunity] )
        ),
    1,
    0
)
Count of first occurence of campaign per opportunity = 
CALCULATE (
    SUMX ( attribution, [Is first occurence of campaign per opportunity] )
)

 image.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @mdfaster,

 

Please try out these two new measures.

Is first new =
VAR maxDays =
    CALCULATE (
        MAX ( attribution[days] ),
        ALLSELECTED ( attribution[days] ),
        ALL ( attribution[campaign] ),
        ALLSELECTED ( attribution[campaign] )
    )
RETURN
    IF ( MAX ( attribution[days] ) = maxDays, 1, 0 )
count new =
SUMX (
    SUMMARIZE (
        attribution,
        attribution[opportunity],
        "maxd", MAX ( attribution[days] )
    ),
    [Is first new]
)

Count_of_first_occurrence_of_an_event_per_group_with_a_user_filter

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Frequent Visitor

Hi all, I'am struggling with calculation so it takes into account a user filter (for example slicer) on source data.

 

Basically, I am trying to create a measure which represents first-click campaign attribution weight. In data language, it means to count campaigns which occured first per opportunity. The first is the campaign with highest number of days to conversion.


I have data like this:

image.png

I have created following two measures and they work okay until user filters data 😕 . I'd like to have  the source data filtered based on the slicers, not the results. I tried to replace ALL with ALLSELECTED and many more tweaks but it doesn't work.

 

See screenshot or attached Sample PBIX workbook, there are sample data and expected result.

 

Thanks for any help Smiley Happy

 

Is first occurence of campaign per opportunity = 
IF (
    SUM ( attribution[days] )
        = CALCULATE (
            MAX ( attribution[days] ),
            ALL ( attribution ),
            VALUES ( attribution[opportunity] )
        ),
    1,
    0
)
Count of first occurence of campaign per opportunity = 
CALCULATE (
    SUMX ( attribution, [Is first occurence of campaign per opportunity] )
)

image.png

 

Hi @mdfaster,

 

Please try out these two new measures.

Is first new =
VAR maxDays =
    CALCULATE (
        MAX ( attribution[days] ),
        ALLSELECTED ( attribution[days] ),
        ALL ( attribution[campaign] ),
        ALLSELECTED ( attribution[campaign] )
    )
RETURN
    IF ( MAX ( attribution[days] ) = maxDays, 1, 0 )
count new =
SUMX (
    SUMMARIZE (
        attribution,
        attribution[opportunity],
        "maxd", MAX ( attribution[days] )
    ),
    [Is first new]
)

Count_of_first_occurrence_of_an_event_per_group_with_a_user_filter

 

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Supeeerb, thank you Dale @v-jiascu-msft, it is exactly what I needed, you are my hero 😉

 

It even works universaly when I add new columns and use them for filtering! So simple yet powerful.

Thanks again.

Michal

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors