Forum Discussion
Return specific value for the first unique record within a date range
Hello all,
my table has records of log numbers and their date of use.
What I want is to include a column that returns a value of 1 for the first unique log number within a month, and 0 if it finds the log number again in the same month. Example:
As you can see, log number "71481" was used once in 1.01.2022 and twice in 1.02.2022, you can see the count is 1 for the first unique value in the same date, and 0 if it occurs again in the same date. So if the value is present again in a different month, the first record of that value is marked as 1 and the rest are 0.
I hope I'm making sense, I know I need to make an index column first in power query, but I need support with that as well.
Any help? Is this possible?
EdK99 , Measure approch
countrows(summarize(Table, Table[Date], Table[Record]) )
if you need a flag then first add an index column in power query
Power BI- Power Query Table.AddIndexColumn- https://youtu.be/KEW4bbuqbV8
then you can create new columns in DAX
rank = rankx(filter(Table, [Date] =earlier([Date]) && [Record] = earlier([Record]) , [index])
flag = if([Rank]=1, 1, 0)
2 Replies
- amitchandak
Super User
EdK99 , Measure approch
countrows(summarize(Table, Table[Date], Table[Record]) )
if you need a flag then first add an index column in power query
Power BI- Power Query Table.AddIndexColumn- https://youtu.be/KEW4bbuqbV8
then you can create new columns in DAX
rank = rankx(filter(Table, [Date] =earlier([Date]) && [Record] = earlier([Record]) , [index])
flag = if([Rank]=1, 1, 0)
That worked!!! thanks a lot!
