Forum Discussion
Helper IIFirst occurence based on two filters
Hi there
I have a table where i store multiple appoiments for the same clients.
I need to calculate something called KPI4, which means the first appoiment where AppType is 2,3 or 5 and Attendance is 5 or 6.
Below in green is my table and i want the output as KPI_4 in red.
Doesnt necesarily have to be a calculated column, can be a measure as well.
I've been trying all day and i can't figure out how to use Min(Appoiment) with AppType and Attendance.
| ID | APPOINTMENT | APPTYPE | ATTENDANCE | KPI_4 |
| 859496 | 10/07/2017 | 2 | 3 | |
| 859496 | 14/07/2017 | 2 | 6 | 1 |
| 859496 | 17/07/2017 | 2 | 3 | |
| 888470 | 20/07/2017 | 3 | 5 | |
| 888470 | 10/07/2017 | 2 | 5 | 1 |
| 891491 | 05/07/2017 | 2 | 5 | 1 |
| 891667 | 04/08/2017 | 3 | 5 | |
| 891667 | 05/07/2017 | 2 | 6 | 1 |
| 891667 | 03/07/2017 | 3 | 2 | |
| 891667 | 06/07/2017 | 2 | 5 | |
| 901759 | 12/07/2017 | 3 | 5 | |
| 901759 | 18/07/2017 | 2 | 6 | |
| 901759 | 03/07/2017 | 5 | 5 | 1 |
| 907876 | 03/08/2017 | 2 | 5 | |
| 907876 | 06/07/2017 | 3 | 5 | 1 |
Hello mihaita_baro
You can create a column like this:
KPI_5 = VAR a = IF ( COUNTX ( FILTER ( ALL ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) ), [ID] ) >= 2, 1 ) RETURN IF ( a = 1 && [APPTYPE] IN { 2, 3, 5 } && [ATTENDANCE] IN { 5, 6, BLANK () }, 1 )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _ Janey
7 Replies
- parry2k
Super User
mihaita_baro try this measure
First Appt = VAR __date = CALCULATE ( MAX ('Table'[APPOINTMENT] ), TOPN ( 1, FILTER ( ALLEXCEPT ('Table','Table'[ID] ), 'Table'[ATTENDANCE] IN { 5, 6 } && 'Table'[APPTYPE] IN { 2, 3, 5 } ), MAX ('Table'[APPOINTMENT] ), ASC ) ) RETURN IF ( MAX ('Table'[APPOINTMENT] ) = __date, 1 ) + 0✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
mihaita_baroHi
Thanks for your help but nothing comes up, it's all blank.
CNENFRNLCommunity Champion
- mihaita_baro
Hi,
I am getting the folowing error message.
- v-janeyg-msft
Community Support
Hi, mihaita_baro
You wrote return part in the maxx function, which caused an error.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _ Janey
