cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dom99
Advocate III
Advocate III

Create measure for first occurrence of event

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 DatePerson_NameProduct NameTotal SalesFirst Occurrence
03/05/2021Bob SmithBooks11
03/05/2021Bob SmithGames21
03/05/2021Oliver JonesBooks21
03/05/2021Oliver JonesGames11
03/05/2021Rory JacobsGames11
10/05/2021Bob SmithAliens11
10/05/2021Bob SmithBooks10
10/05/2021Oliver JonesBooks20
10/05/2021Rory JacobsAliens11
17/05/2021Bob SmithGames10
17/05/2021Rory JacobsGames10


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)

 

dom99_0-1623229483036.png

 

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

 

dom99_0-1623230936236.png

 

Any help will be very much appreciated.

 

Kind Regards

Dominic

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

View solution in original post

17 REPLIES 17
ERD
Super User
Super User

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 ) )

ERD_0-1623311437135.png

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

dom99
Advocate III
Advocate III

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

 

 

@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
Advocate III
Advocate III

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

dom99_0-1623323595156.png

 

@dom99 ,

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

ERD_0-1623339778282.png

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

dom99
Advocate III
Advocate III

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

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

View solution in original post

dom99
Advocate III
Advocate III

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 🙂

 

dom99_0-1623397341659.png


Again many thanks for all your help 🙂

@dom99 ,

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

ERD_0-1623399759764.png

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

dom99
Advocate III
Advocate III

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.

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
Advocate III
Advocate III

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
Advocate III
Advocate III

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

amitchandak
Super User
Super User

@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)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

 

dom99_0-1623235831518.png

 

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
)

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.

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.