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
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 |
Solved! Go to 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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |