Count the number of re-entries

[Anonymous]

Hey everyone,

I'm stuck at a new problem and need some help. I'm supposed to count the number of times a product has been entered into our database. I have looked through similar quesitons like this one and this one but I couldn't apply either onto my situation so I wanted to ask in a new question.

My situation looks like this:

FrameID	EntryDate	SubIndex	EntryCount
THXY21	1.1.2020	1	1
THXY21	1.1.2020	5
THXY21	1.1.2020	6
THXY24	4.2.2020	1	1
THXY24	4.2.2020	5
THXY25	5.2.2020	1	2
THXY25	5.2.2020	6
THXY25	5.2.2020	5
THXY25	8.4.2020	5	(2?)
THXY25	8.4.2020	1
THXY25	8.4.2020	6

(In reality there are more rows and columns which I will leave out for clarity)

I am now supposed to count the number of times each FrameID has been entered and then I'm supposed to visualize the results (I was thinking about a bar chart but for now I first need to figure out how to count the entries). Each FrameID gets one entry in the data base, with a date, when the product is built. Afterwards, if a product breaks and gets taken to a repairshop then a second entry gets created, with a new date. If it breaks again it goes back to the shop and another entry is created and so on (I think all but maybe 5 FrameIDs have entries from at most two different dates). So THXY25 was built on 5.2.2020 and then taken to a repairshop on 8.4.2020. So THXY21 and THXY24 were entered once, THXY25 was entered twice. I need some way of counting this, maybe in a calculated column or something. I wanted to try to enter the amount of entries of this FrameID in the first row of the corresponding FrameIDs but I wasn't sure if thats even possible and I was not sure if that is the best way to visualize things later (I will happily consider any of your guys ideas on how to best enter the data). The two other answers I linked look like they are sort of similar, but I was not able to apply them onto my problem due to the nature of my database, where each product has multiple rows corresponding to it, and might only be distinguishable by date. I hope my question makes sense.

If anyone has any ideas I would be very thankful. Thanks in advance!

[ryan_mayu · Accepted solution]

@Anonymous 

you can create an index column.(make sure you data sort by frame ID)

Then create a new column

Column = 
VAR _id=maxx(FILTER(FrameEntries,FrameEntries[Index]=EARLIER(FrameEntries[Index])-1),FrameEntries[FrameID])
return if('FrameEntries'[FrameID]<>_id,CALCULATE(DISTINCTCOUNT(FrameEntries[EntryDate]),ALLEXCEPT(FrameEntries,FrameEntries[FrameID])),blank())

if you want to show count for 5/2 and 8/4, try this

Column = 
VAR _date=maxx(FILTER(FrameEntries,FrameEntries[Index]=EARLIER(FrameEntries[Index])-1),'FrameEntries'[EntryDate])
return if('FrameEntries'[EntryDate]<>_date,CALCULATE(DISTINCTCOUNT(FrameEntries[EntryDate]),ALLEXCEPT(FrameEntries,FrameEntries[FrameID])),blank())

[Anonymous]

@ryan_mayu This seems pretty much spot on for what I need, thank you! But for some reason when I create the index column and then enter your code it seems to reorganize the index column in some rows and I just cannot figure out why that is. I will keep playing around with it and report back.

[Anonymous]

Try this calculated column

EntryCount =
VAR CurFrameID = FrameEntries[FrameID]
VAR AggregatedTable =
    ALL ( FrameEntries[FrameID], FrameEntries[EntryDate] )
VAR EntryCount =
    COUNTROWS ( FILTER ( AggregatedTable, FrameEntries[FrameID] = CurFrameID ) )
RETURN
    EntryCount

[Anonymous]

@Anonymous Thanks for your fast answer! This seems to work, however it writes the "EntryCount" in every row, rather than only in one of the rows corresponding to a FrameID. I was trying to maybe write a IF query before your code and then set it so that it only enters the count number in the calculated column if it is the earliest entry of this FrameID, but so far that hasn't worked. Do you have an idea how I could achieve that? This is what it currently looks like:

THXY21	1.1.2020	1	1
THXY21	1.1.2020	5	1
THXY21	1.1.2020	6	1
THXY24	4.2.2020	1	1
THXY24	4.2.2020	5	1
THXY25	5.2.2020	1	2
THXY25	5.2.2020	6	2
THXY25	5.2.2020	5	2
THXY25	8.4.2020	5	2
THXY25	8.4.2020	1	2
THXY25	8.4.2020	6	2

I would prefer it to be only the red entries but I can't seem to achive that through IF queries. 

Thanks again!