Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
mahoneypat
Employee
Employee

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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!

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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?

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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.

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.