cancel
Showing results for
Did you mean:
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.

 Index ID Date Prescription Switched From Switched to 3. prescription 1 1 17.10.2020 Drug 1 Drug1 2 1 17.10.2020 Drug 1 Drug 1 Drug 1 Drug2 3 1 18.10.2020 Drug 2 Drug1 Drug 2 Drug3 5 1 19.10.2020 Drug 3 Drug2 Drug3 6 2 20.10.2020 Drug2 Drug1 7 2 20.10.2020 Drug1 Drug2 Drug1 Drug1 8 2 22.10.2020 Drug1 Drug1 Drug1
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

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.

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

Proud to be a Super User!

7 REPLIES 7
Highlighted
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

Proud to be a Super User!

Highlighted
Regular Visitor

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
Super User III

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

Proud to be a Super User!

Highlighted
Regular Visitor

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
Super User III

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

Proud to be a Super User!

Highlighted
Regular Visitor

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

 ID Date ATC ryhmä Index Switched from Switched to Next from switched to 1 01/06/2020 0.00 SGLT2 estäjät 620 1 01/07/2020 0.00 Short insulin 621 SGLT2 Short insulin GLP-1-analog (from the index 625) 1 01/07/2020 0.00 Long insulin 622 1 01/07/2020 0.00 Metformin 623 1 01/07/2020 0.00 GLP-1-analog 624 1 01/08/2020 0.00 SGLT2 625 GLP-1-analog SGLT2 1 01/08/2020 0.00 GLP-1-analog 626 1 01/08/2020 0.00 Metformin 627

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
Super User III

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.

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

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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