Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate minimum date issue in Live Mode data connection

Hi Experts,

Is there a way I can achieve the below while having my datasource connection in Live mode?

The below code works when I use excel data source but it fails to recognise the Calculate function in live mode connection.

 

 

Expected Result = CALCULATE(MIN(DispenseTable[Date]),FILTER(DispenseTable,DispenseTable[PatientId]=EARLIER(DispenseTable[PatientId])&&DispenseTable[MP]=EARLIER(DispenseTable[MP])&&DispenseTable[Date]>EARLIER(DispenseTable[Date])))

 

 

Data

 

PatientIdDateDrugIDMP (from DrugTable)Expected Result
1647986/01/2022 0:004802Medicine 118/01/2022 0:00
1647988/01/2022 0:004802Medicine 1110/01/2022 0:00
16479810/01/2022 0:004664Medicine 1030/01/2022 0:00
16479810/01/2022 0:004704Medicine 10 
16479810/01/2022 0:004802Medicine 1112/01/2022 0:00
16479812/01/2022 0:004802Medicine 1114/01/2022 0:00
16479814/01/2022 0:004802Medicine 1118/01/2022 0:00
16479818/01/2022 0:004802Medicine 1119/01/2022 0:00
16479819/01/2022 0:004795Medicine 1121/01/2022 0:00
16479819/01/2022 0:004802Medicine 11 
16479821/01/2022 0:004802Medicine 1127/01/2022 0:00
16479824/01/2022 0:00-100Non Coded Monitored Drug21/02/2022 0:00
16479827/01/2022 0:004802Medicine 1130/01/2022 0:00
16479830/01/2022 0:004704Medicine 107/02/2022 0:00
16479830/01/2022 0:004794Medicine 111/02/2022 0:00
1647981/02/2022 0:004802Medicine 117/02/2022 0:00
1647985/02/2022 0:005411Medicine 133/05/2022 0:00
1647987/02/2022 0:004704Medicine 102/03/2022 0:00
1647987/02/2022 0:004802Medicine 119/02/2022 0:00
1647989/02/2022 0:004802Medicine 1110/02/2022 0:00
16479810/02/2022 0:004802Medicine 1111/02/2022 0:00
16479811/02/2022 0:004802Medicine 1113/02/2022 0:00
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure =
VAR Maxdate =
    CALCULATE (
        MAX ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
VAR Mindate =
    CALCULATE (
        MIN ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && DispenseTable[Date] > SELECTEDVALUE ( DispenseTable[Date] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( DispenseTable[Date] ) = Maxdate, BLANK (), Mindate )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Anonymous 

    maybe you can try to create a measure

    Measure = minx(FILTER(all('DispenseTable'),DispenseTable[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]>max('DispenseTable'[Date])),'DispenseTable'[Date])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi ryan_mayu 

       

      Thanks heaps for providing me that code.

      It works great for all of the rows except for the last row.

      The last row for any medicine should be null when there are no further records for that medicine.

      But I see some value picked for each medicine in the last row.

       

      Appreciate your further help here.

       

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        Anonymous 

        pls try this

        Measure = 
VAR _max=CALCULATE(max('DispenseTable'[Date]),ALLEXCEPT(DispenseTable,DispenseTable[PatientId],DispenseTable[MP]))
VAR _max2=CALCULATE(max('DispenseTable'[DrugID]),FILTER(ALL(DispenseTable),'DispenseTable'[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]=_max),DispenseTable[DrugID])
return if(MAX('DispenseTable'[DrugID])=_max2&&MAX('DispenseTable'[Date])=_max,blank(),minx(FILTER(all('DispenseTable'),DispenseTable[PatientId]=max(DispenseTable[PatientId])&&DispenseTable[MP]=max(DispenseTable[MP])&&'DispenseTable'[Date]>max('DispenseTable'[Date])),'DispenseTable'[Date]))

        pls see the attachment below

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure =
VAR Maxdate =
    CALCULATE (
        MAX ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
VAR Mindate =
    CALCULATE (
        MIN ( DispenseTable[Date] ),
        FILTER (
            ALL ( DispenseTable ),
            DispenseTable[PatientId] = SELECTEDVALUE ( DispenseTable[PatientId] )
                && DispenseTable[MP] = SELECTEDVALUE ( DispenseTable[MP] )
                && DispenseTable[Date] > SELECTEDVALUE ( DispenseTable[Date] )
                && [DrugID] = SELECTEDVALUE ( DispenseTable[DrugID] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( DispenseTable[Date] ) = Maxdate, BLANK (), Mindate )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.