Forum Discussion

mihaita_baro's avatar
mihaita_baro
Helper II
4 years ago
Solved

First 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 Atten...
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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's avatar
parry2k
Super User
4 years ago

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's avatar
    mihaita_baro
    Helper II
    4 years ago

    Hi

     

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