Count Row Only if it contains the first instance of a value, PER unique Contact Identifier

[EAnthemOPS]

Hey! I may be overthinking this, but I'm having an issue with counting rows in my data set. Essentially, I only want to count a row if it contains the first instance of a 'NewStage' value equaling "Stage1" - but I need to count the first time it happened for each unique Contact (represented by the 'ContactID' column in the datatable). I'll include a (hopefully legible) write-up below that might help better explain everything - any help would be appreciated!

SetUP:

In the StageHistories Table, I have the following columns:

• HistoryID = unique identifier for each row
• ContactID = identifies who the History record belongs to – this ID occurs multiple times in the table.
• OldStage = Key for the name of the Old Stage.
• NewStage = Key for the name of the New Stage.
• OldSubStage = Key for the name of the old sub-stage.
• NewSubStage = Key for the name of the new sub-stage.
• ChangedDate = the date that the record’s stage was changed. Each HistoryID has a specific ChangeDate (though some records may be changed at the exact same time, they would still be unique based on ContactID)
• Trigger = key to identify what triggered the change
• CompanyID = Key for the name of the company that owns the History Record.

NOTE: All columns labeled as key can be filtered using the Key Value, OR I can create LOOKUPVALUE columns that actually contain the text name of each value (and use those columns in place of the ‘Key’ columns.)

Need:

I need to count the number of HistoryID rows that match the following criteria:

NewStage = ‘Stage1’

Trigger = ‘TriggerX’

BUT I only want to count the row if it is the first time that a unique ContactID has ever been moved to a NewStage of ‘Stage1’.

EX:

Johnny A (who would be represented by the ContactID value) has 6 HistoryID records (rows in the table) that contain a NewStage value of ‘Stage1’. The first one occurred in January of 2018, and the next 5 all occurred in February.

When visualizing the count of these rows for February, all 5 of the HistoryID records (rows) with Johnny A’s name would NOT be included in the count, because they were not the first instance of Johnny A’s new stage equaling ‘Stage1’. When visualizing for both January and February, Johnny A should only contribute 1 count to the total number of History ID records being counted and should still be shown as occurring on a specific date in January.

Hopefully, all that makes some sense! Thanks in advance.

[Greg_Deckler]

I would think that you would want to add an Index column in Power Query to your table. Then in a column, you should be able to CALCULATE the MAX of your ContactID and create a temp table using ALL that is filtered to your criteria. Then, grab the MIN from that table of your Index and compare the current row index. If they match, RETURN a 1, otherwise BLANK. 

Sample data would help me be more specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490