Counting first occurences

Hello,

I have a database of schools and events to which they have particpated. The database is attached with school Id, date of program and source of information.

What I am trying to do is build a table with a count of schools in the first instance for each information source. Inasmuch, as a school may participte several times, I wish to exclude subsequent events and the source.

As you can see on the attache file I have built a measure to count the events and the number of unique schools for each information source but I can’t get to the next step. I understand that the school id’s need to be filtered to a single occurence. But, vould someone please show me how.
First Occurence.pbix (73.4 KB)

Much appreciated and thank you in advance

Michael Gordon

Hello,

I forgot to mention in the original post that I added a calculated column for the First occurence date.

After writing the post, I had a thought that if I created another calculated column as a flag to effectively isolate the first occurence from all subsequent occurence’s I could then create a filter. In this updated file you, will see that I did build a another calculated column (a flag with 1 for the original occurence and 0 for susequent occurence’s) and then a new measure called first.

First = CALCULATE([Unique ID],

FILTER(‘First Program Data’,

‘First Program Data’[First Occurence] = 1))

From the table, you can see that I have improved the result but duplicate occurences are still being counted. For example, School ID P020 is being counted in four (4) sources. A school ID should only appear in 1 source, the source of the first occurence.

Appreciate any help with this problem.

Thank You

Michael Gordon
First Occurence.pbix (74.7 KB)

@MIchaelGordon ,

Can you please post the underlying data file as well? I think this problem may lend itself better to a Power Query approach than a DAX solution.

Thanks.

– Brian

Hi BRian

Both files are now attached.

First Occurence.pbix (74.2 KB)
First Occurence.xlsx (5.4 MB)

@MIchaelGordon ,

If I understand your requirement properly, this should be the solution you’re looking for:

This is a perfect example of how good data prep makes DAX incredibly simple. Here’s the only DAX I wrote for this – one measure and no calculated columns:

Count Rows First Source Only = COUNTROWS( ‘First Source Only’ )

In terms of data prep, I did the following steps:

– Duplicated the base table
– Grouped on school then calculated minimum date per school

  • created a custom function where if minimum date = program date then I got a 1 else 0
    – filtered on only the rows where that custom function was equal to 1
    – filtered out all the duplicates (five schools where they had multiple events of the same type on the same minimum date

I hope this is helpful – full solution file attached below.

– Brian

e DNA Forum – First Occurence Solution.pbix (104.4 KB)

2 Likes

Thanks Brian,

I was finally able to replicate your table. The problem I had was that after I copied the original table and then Grouped By, I was only left with 2 columns: Schools ID and First Date. I then needed to merge this copy with the orginal table to create a third working table from where I could do the custom function.

I couldn’t look at your Power Query as it couldn’t access your data file.

Good learning experience but did I miss something or misstep?

Thanks again.

Michael Gordon

@MIchaelGordon ,

We are using the same data file, just with a different path. To change the path in power query just follow these three steps, and then browse to the First Occurrence.xlsx data file on your computer.

Once you’ve done that, you’ll be able to browse my steps in PQ. You’ll see that your grouping step is different in that I used the All Rows operation in addition to the minimum. This is what maintained the columns in my table.

If you’re not familiar with the All Rows operation, I did a recent video on it and in combination with Group By, It’s one of the most powerful functions in Power BI and can be used to easily solve almost any grouping related problem.

Sorry – should have been clearer about this in the email with my initial solution.

Hope this gets you what you need. Just give a shout if it doesn’t.

– Brian

1 Like

Thanks much appreciated.

MG

1 Like

Hi @MIchaelGordon , did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Hi @MIchaelGordon, we’ve noticed that no response has been received from you since the 15th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @MIchaelGordon, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

My apologies for not responding.

The solution from Brian was fine. Thanks again.

MG