Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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])
Proud to be a Super User!
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?
Proud to be a Super User!
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.
you only have 2 ID, how locaition 1 is 2 and locaion 2 is 1, totally 3?
Proud to be a Super User!
I made a mistake. I've fixed it. I'd be happy to hear if you have a solution?
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])
Proud to be a Super User!
Yea this is the solution I came up with as well. Just with more steps. Thanks
you are welcome
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |