cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Get the last, next and third value of same ID

Hi, I am facing a very difficult problem.  I need to build calculated column to get the 3. value of same ID. It would the next non blank value of the "Switched From" column with the same ID and (different date actually). If there is other way than calculated column to calculate this, please share it too.

I need to calculate the distinct count of ID's having same combinations, for example Drug1-Drug2-Drug1. Data is more than 1M rows.

 

IndexIDDatePrescriptionSwitched FromSwitched to3. prescription
1117.10.2020Drug 1 Drug1 
2117.10.2020Drug 1Drug 1Drug 1Drug2
3118.10.2020Drug 2Drug1Drug 2Drug3
5119.10.2020Drug 3Drug2Drug3 
6220.10.2020Drug2 Drug1 
7220.10.2020Drug1Drug2Drug1Drug1
8222.10.2020Drug1Drug1Drug1 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Here are three column expressions to seem to work with your new example data to give the result below (last 3 columns match your example 3 columns).  It's a bit hacky, so will be interested to hear from you if it works with the real data.

 

mahoneypat_0-1603033457339.png

 

Switched From =
VAR thisindex = Drugs2[Index]
VAR thisdate = Drugs2[Date]
VAR previndex =
    CALCULATE (
        MAX ( Drugs2[Index] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Index] < thisindex
    )
RETURN
    CALCULATE (
        MIN ( Drugs2[ATC ryhmä] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] <> thisdate,
        Drugs2[Index] = previndex
    )

 

 

Switched To =
IF (
    ISBLANK ( Drugs2[Switched From] ),
    BLANK (),
    Drugs2[ATC ryhmä]
)

 

Next Switched To =
VAR thisindex = Drugs2[Index]
VAR thisdate = Drugs2[Date]
VAR nextindex =
    CALCULATE (
        MIN ( Drugs2[Index] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] > thisdate
    )
VAR nextdrug =
    CALCULATE (
        MIN ( Drugs2[Switched From] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] <> thisdate,
        Drugs2[Index] = nextindex
    )
RETURN
    IF (
        ISBLANK ( Drugs2[Switched From] ),
        BLANK (),
        nextdrug
    )

 

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Please clarify.  Is your desired output the column called 3. Prescription?  Or the distinct count mentioned?  Please show what the output should be from your example data.

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted

Hi! The outcome here is to get those columns, because I think then I can calculate the outcomes with measure

Let's say measure 1: "Calculate(DistinctCount(ID),SwitchedFrom="Drug1",SwitchedTo="Drug2",3.Prescription="Drug1")"

So if I have the columns I can get the results by creating a measure based on these new columns.

 

Thank you so much!

Highlighted

Looks like one of your columns is the same value as the Prescription on that row, so here are two column expressions to get the other two rows.

 

From =
VAR thisindex = Drugs[Index]
VAR previndex =
    CALCULATE (
        MAX ( Drugs[Index] ),
        ALLEXCEPT (
            Drugs,
            Drugs[ID]
        ),
        Drugs[Index] < thisindex
    )
RETURN
    CALCULATE (
        MIN ( Drugs[Prescription] ),
        ALLEXCEPT (
            Drugs,
            Drugs[ID]
        ),
        Drugs[Index] = previndex
    )

 

To =
VAR thisdrug = Drugs[Prescription]
VAR thisindex = Drugs[Index]
VAR nextindex =
    CALCULATE (
        MIN ( Drugs[Index] ),
        ALLEXCEPT (
            Drugs,
            Drugs[ID]
        ),
        Drugs[Index] > thisindex
    )
VAR nextdrug =
    CALCULATE (
        MIN ( Drugs[Prescription] ),
        ALLEXCEPT (
            Drugs,
            Drugs[ID]
        ),
        Drugs[Index] = nextindex
    )
RETURN
    IF (
        thisdrug <> nextdrug,
        nextdrug
    )

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted

Hi Pat,

 

thanks for the help! I appreciate it a lot. However, I still have to calculate the 3. drug, just like the From and To. Do you have any ideas on that?

Highlighted

The From and To expressions I suggested get the 1st and 3rd of your columns.  The 2nd one seems to match the Prescription column so I thought it was unnecessary.  What is the logic for the 2nd column (2nd to last)?

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted

Hello,

 

The idea here is that the "From" column may be empty, if there is no switch OR the switch has happened on the same day. Like this

IDDateATC ryhmäIndexSwitched fromSwitched toNext from switched to
101/06/2020 0.00SGLT2 estäjät620   
101/07/2020 0.00Short insulin621SGLT2Short insulinGLP-1-analog (from the index 625)
101/07/2020 0.00Long insulin622   
101/07/2020 0.00Metformin623   
101/07/2020 0.00GLP-1-analog624   
101/08/2020 0.00SGLT2625GLP-1-analogSGLT2 
101/08/2020 0.00GLP-1-analog626   
101/08/2020 0.00Metformin627   

 

So the row 621 should have the value of "Switched From", from the index 625. So it would be first non blank value after the current index in the column.

Highlighted

Here are three column expressions to seem to work with your new example data to give the result below (last 3 columns match your example 3 columns).  It's a bit hacky, so will be interested to hear from you if it works with the real data.

 

mahoneypat_0-1603033457339.png

 

Switched From =
VAR thisindex = Drugs2[Index]
VAR thisdate = Drugs2[Date]
VAR previndex =
    CALCULATE (
        MAX ( Drugs2[Index] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Index] < thisindex
    )
RETURN
    CALCULATE (
        MIN ( Drugs2[ATC ryhmä] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] <> thisdate,
        Drugs2[Index] = previndex
    )

 

 

Switched To =
IF (
    ISBLANK ( Drugs2[Switched From] ),
    BLANK (),
    Drugs2[ATC ryhmä]
)

 

Next Switched To =
VAR thisindex = Drugs2[Index]
VAR thisdate = Drugs2[Date]
VAR nextindex =
    CALCULATE (
        MIN ( Drugs2[Index] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] > thisdate
    )
VAR nextdrug =
    CALCULATE (
        MIN ( Drugs2[Switched From] ),
        ALLEXCEPT (
            Drugs2,
            Drugs2[ID]
        ),
        Drugs2[Date] <> thisdate,
        Drugs2[Index] = nextindex
    )
RETURN
    IF (
        ISBLANK ( Drugs2[Switched From] ),
        BLANK (),
        nextdrug
    )

 

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors