Forum Discussion

rhl94's avatar
rhl94
Advocate III
6 years ago

Find ID in multiple rows

Hi

I want a calculated column or measure, that determines whether or not an identical treatment has been given to the same customer before, without a period of 3 years.

 

I have the following columns:

Claim | PersonID | Treatmenttype | Date
1      1      2356      1-1-2020
2      1      2356      1-3-2018
3      2      2356      1-2-2020
4      2      2356      1-1-2016
5      3      2685      23-2-2017

 

In this case, I want a flag on PersonID 1 as identical treatment(2353) has been giving more than once within the period of 3 years, while PersonID 2 has more than 3 years between the same treatment

 

Need Help

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi rhl94 

    try a measure

     

    Measure = 
var _isInCondition = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[PersonID]=SELECTEDVALUE('Table'[PersonID]) && 'Table'[Treatmenttype]=SELECTEDVALUE('Table'[Treatmenttype]) && 'Table'[Date]< SELECTEDVALUE('Table'[Date]) && DATEDIFF('Table'[Date], SELECTEDVALUE('Table'[Date]),YEAR) < 4))
RETURN
if(_isInCondition>0,1,0)

     

     

    • rhl94's avatar
      rhl94
      Advocate III
      6 years ago

      Seems like I forgot to mention that the treatmenttype column comes from a different table so that Customer (one) to Treatment (many).

       

      Therefore the measure doesnt quite work. If I remove the filter on Treatment, it seems to work.

       

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi rhl94 

         

        Try this measure.

        Measure = 
CALCULATE(
    IF( 
        COUNTROWS( 'Table' ) > 1, 
        DISTINCTCOUNT( 'Customer'[PersonID] ) 
    ),
    ALLEXCEPT( 'Table', 'Customer'[PersonID], 'Table'[Treatmenttype] ),
    DATESINPERIOD( 'Calendar'[Date], MAX( 'Calendar'[Date] ), -3, YEAR )
)

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn

         

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try as new column

    if ( datediff(maxx(filter(table,table[PersonID]=earlier[PersonID] && table[Treatmenttype] =earlier[Treatmenttype]) &&
    table[Date] =earlier[Date]) ,table[Date],table[Date],year)<=3 ,"Yes","No")