First occurence based on two filters

[mihaita_baro]

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

[CNENFRNL · Accepted solution]

[CNENFRNL · Accepted solution]

[mihaita_baro]

Hi,

I am getting the folowing error message.

[v-janeyg-msft]

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

[mihaita_baro]

THanks, now it worked.

I have another KPI5 to calculate which means Clients must have at least 2 appoiments where AppType is 2,3 or 5 and Attendance is 5 or 6. or blank.

Not sure how i can count the appoiments based on multiple filters

[v-janeyg-msft · Accepted solution]

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

[parry2k]

@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_baro]

Hi

Thanks for your help but nothing comes up, it's all blank.