cancel
Showing results for
Did you mean:

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

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

1 ACCEPTED SOLUTION
Super User

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

17 REPLIES 17
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 ) )``````

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

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

Super User

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.

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

Super User

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.

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

Super User

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

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 🙂

Super User

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.

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.

Super User

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.

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

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

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)

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.

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!