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