Create measure for first occurrence of event

[dom99]

Hi everyone,

I would really appreciate some help from this great community. I have looked at many examples and other posts for days but not had any luck solving my problem.

I have attached a .pbix file with example data (in a star schema) if anyone wants to try it out, the data below is based on the same data in that .pbix file.

Power BI Example File 

I want to calculate the number of repeat sales for each person and product. I plan to do this by having a measure:
total sales - first sale occurance. These need to be measures as the dataset this will run over is very large and creating new columns will most likely cause memory issues.

However I'm struggling to calculate the first sale occurance

I have the following data

Week Start Date	Person_Name	Product Name	Total Sales	First Occurrence
03/05/2021	Bob Smith	Books	1	1
03/05/2021	Bob Smith	Games	2	1
03/05/2021	Oliver Jones	Books	2	1
03/05/2021	Oliver Jones	Games	1	1
03/05/2021	Rory Jacobs	Games	1	1
10/05/2021	Bob Smith	Aliens	1	1
10/05/2021	Bob Smith	Books	1	0
10/05/2021	Oliver Jones	Books	2	0
10/05/2021	Rory Jacobs	Aliens	1	1
17/05/2021	Bob Smith	Games	1	0
17/05/2021	Rory Jacobs	Games	1	0

The column at the end in bold is the one I need to generate, the first occurance of a sale per person and product.

The calculation needs to take into account on the page will be slicers for the date range, product name and person name. 

The totals of the measures (the total row at the bottom of the table visual for example) need to be in the context of the person and product.

The end result will be displayed in a matrix visual like in the .pbix file and screenshot below (the bottom visual)

Everything I try doesnt work because when displaying the result the row context splits it by the date, so I end with with the first in the row context of the visual.

For reference here is the data model from the example pbix file linked above

Any help will be very much appreciated.

Kind Regards

Dominic

[ERD]

Hi @dom99 ,

Here is the measure you can try to calculate the first occurence:

firstD = 
VAR cPerson = MAX ( 'Fact Sales'[Person_ID] )
VAR cProduct = MAX ( 'Fact Sales'[Product_ID] )
VAR firstD =
    CALCULATE (
        MIN ( 'Fact Sales'[Date_ID] ),
        FILTER (
            ALLSELECTED ( 'Fact Sales' ),
            'Fact Sales'[Person_ID] = cPerson && 'Fact Sales'[Product_ID] = cProduct
        )
    )
VAR currD = MIN ( 'Fact Sales'[Date_ID] )
RETURN
    IF ( COUNT ( 'Fact Sales'[Date_ID] ) > 0, IF ( firstD - currD = 0, 1, 0 ) )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[dom99]

Hi @ERD ,

Thank you so much that is a great answer I really appreciate it! 

One thing, do you know how I can get the column total to respect the column content? So in the great example you posted it would say a total of 7?

I've tried playing around with your formula but would appreciate some guidance. In the past I've used something like a SUMX over VALUES but I cant see how to implement that here.

Many thanks

[ERD]

@dom99 ,

You can use similar approach, but with 3 columns:

result =
IF (
    HASONEVALUE ( 'Dim Date'[Week Start Date] ),
    [firstD],
    SUMX (
        SUMMARIZE (
            'Fact Sales',
            'Dim Date'[Week Start Date],
            'Dim Product'[Name],
            'Dim Person'[Person_Name]
        ),
        [firstD]
    )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[dom99]

@ERD  Wow brilliant! Thats great! I plugged it in and off it goes. I've ordered the difinitive guide to Dax book this morning to better understand how that works.

One thing which I dont understand, In the screenshot below the bottom table I have put the data in a matrix, but in the columns I'm not getting the sum of the values in the table at the top. Any ideas why? Again I'm playing around changing different bits to use SUMX but its not making a difference.

[ERD]

@dom99 ,

Is this what you want to achieve? I'm not quite sure what exactly you want to see.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[dom99]

Hi @ERD 

Yes that's exactly right! Sorry if I wasn't clear. Any ideas on how to do that? Ive tried all afternoon with no success, just can't see what to change.

kind regards

[ERD · Accepted solution]

@dom99 ,

Please, change the second measure:

result2 = 
IF (
    HASONEVALUE ( 'Fact Sales'[Date_ID] ),
    [firstD],
    SUMX (
        SUMMARIZE (
            'Fact Sales',
            'Fact Sales'[Date_ID],
            'Fact Sales'[Person_ID],
            'Fact Sales'[Product_ID]
        ),
        [firstD]
    )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[dom99]

Hi @ERD 

Thanks for all the replies. I'm afraid the column values with the new result2 measure are still showing as 1. Any ideas on how to get the values to sum up on the columns? So in the example below I would like Bob Smith to have a count of 2 for the 3rd May, and that to also be included in the column total of the 3rd May. I hope that makes sense 🙂

Again many thanks for all your help 🙂

[ERD]

@dom99 ,

Are you sure you've chosen the second measure for you second visual?

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[dom99]

How strange, its working now I've started with a fresh copy of the pbix file.

Thats great I'll mark this as the solution. Thanks again its been a big help!

I know I;ve taken a lot of your time already but if you find a minute could you please explain why changing the columns in the summarize from the dimension colums to the fact table columns made that sort of difference? I'm interested to know for future use cases.

[ERD]

Hi @dom99 ,

Sorry, didn't notice at first, but the solution seems to be still incorrect (filter the table by Bob).

Maybe there is some other approach needed, for now can't get one.

[dom99]

Hi @ERD,

Thanks for the update and well spotted. I'm going to come back to it in a couple of days after a re-think. It sounds simple - find the first occurance of an event per person, but the row and filter context, plus the need to filter for different date ranges dynamically make it difficult.

Thanks again for all your help, as I say I'll come back to it soon

[dom99]

Can anyone offer any help? It would be much appreciated!

[amitchandak]

@dom99 , Try a new column like

if([Week Start Date] = minx(filter(Table, [Person_Name] =earlier([Person_Name]) && [Product Name] = earlier([Product Name] )) ,[Week Start Date]),1,0)

[dom99]

I'm afraid the calculated column doesnt seem to be giving the expected results. 

The second row, column called "Column first occurance" I need it to have a 1 in there as its the first occurance of that person and that product.

It might be because that person had 2 sales in that week so in the raw data there were 2 rows

Formula used:

Column First Occurance = 
if('Fact Sales'[Date_ID] =
    minx(
        filter(
            'Fact Sales', 
            'Fact Sales'[Product_ID] = earlier('Fact Sales'[Product_ID]) &&
            'Fact Sales'[Person_ID] = earlier('Fact Sales'[Person_ID]) 
        ) ,
        'Fact Sales'[Date_ID]
    ),
    1,
    0
)

[dom99]

Hi @ERD , 

Thanks for the reply. Unfortunatly the data set this will be run over is quite large, and therefore I would prefer if I could do this using a measure. I suspect using a new calculated column will result in memory issues.

[dom99]

Do you know if that column formula can be re-written as a measure?

Also I've tried implementing the calculated column and I cant make use of the star schema dimension columns in the formula. I had to reference all the ID values from the fact table. This might be problematic as I need to use the week start date which is in the dimension

Thanks again for any responses