Get all values in new columns from duplicate ids

[JayReidy]

Hey

I have a table that has ID that has up to 5 duplicates. It looks like this

ID	Location	Cancelled	No show	Rescheduled	Title	Date
1	Location 1	Yes	No	Yes	Title 1	1/1/23
2	Location 2	No	No	No	Title 2	1/3/23
1	Location 2	No	Yes	No	Title 3	12/2/23
1	Location 1	No	No	No	Title 4	13/3/23
2	Location 1	No	No	No	Title 5	2/3/23

In my matrix I want to be able to see

Location	Unique(by earliest)	Visited again	Cancelled	No show	Rescheduled
Location 1	1	1	1	0	1
Location 2	1	0	0	1	0

I'm having trouble doing this in Power Query or Dax. Any help is appreciated

[ryan_mayu]

what's the logic of calculation?

why unique is 2 for location 1 and 1 for location 2?

what do you mean by visit again? how many ID visit the same location more than once?

[JayReidy]

Sorry Unique should be First visit, wrong naming scheme. Clients will could visit the same venue more than once. I've figured out a very work around way to do this now. 

I've made a bunch of calculated columns for what I need then do a measure to only count the earliest create date from each id.

[ryan_mayu]

you only have 2 ID, how locaition 1 is 2 and locaion 2 is 1, totally 3?

[JayReidy]

I made a mistake. I've fixed it. I'd be happy to hear if you have a solution?

[ryan_mayu · Accepted solution]

@JayReidy 

maybe you can try this

Measure = 
var tbl= FILTER(ADDCOLUMNS('Table',"check",if('Table'[Date]=CALCULATE(min('Table'[Date]),ALLEXCEPT('Table','Table'[ID])),1,0)),[check]=1)
return countx(tbl,[ID])

[JayReidy]

Yea this is the solution I came up with as well. Just with more steps. Thanks

[ryan_mayu]

you are welcome