March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello! I have a table showing data about falls with FileID as the unique row identifier and patient MRN. One MRN may have multiple FileIDs. I created an index column for the rows that starts over with each quarter. I need to return another column that shows the first index value for subsequent falls for the same patient.
For example:
Year | Quarter | Month | YrQtr | FileID | MRN | Index | Desired Output |
2023 | 1 | 3 | 202301 | 4009 | 1234 | 198 | |
2023 | 1 | 3 | 202301 | 4092 | 1234 | 203 | 198 |
2023 | 1 | 3 | 202301 | 4385 | 1234 | 224 | 198 |
2023 | 2 | 4 | 202302 | 5659 | 1234 | 27 | |
2023 | 2 | 4 | 202302 | 5723 | 1234 | 30 | 27 |
2023 | 2 | 4 | 202302 | 5951 | 1234 | 48 | 27 |
2023 | 2 | 4 | 202302 | 5953 | 1234 | 50 | 27 |
2023 | 2 | 4 | 202302 | 6216 | 1234 | 59 | 27 |
Any ideas how to get that? You can see the DAX for a calculated column I tried but it is just returning the same Index value for each row. I'm assuming I don't have it filtred correctly.
FirstIndex2 =
MINX (
FILTER (
FallDetailDIM,
FallDetailDIM[FileID] = EARLIER ( FallDetailDIM[FileID] )
&&
FallDetailDIM[FallMRN] = FallDetailDIM[FallMRN]
),
FallDetailDIM[Index]
)
I also tried the following, which just returned "1" for every row - the very first value in the Index column:
FirstIndex3 =
CALCULATE(
FIRSTNONBLANK( FallDetailDIM[Index], TRUE() ),
FILTER(
FallDetailDIM,
FallDetailDIM[FallMRN] = EARLIER( FallDetailDIM[FallMRN] )
)
)
Any assistance would be much appreciated!
Solved! Go to Solution.
Hi @cathoms,
Probably not the best formula for that case but seems to work 😉
Result =
VAR _minIndex =
MINX(
CALCULATETABLE(FallDetailDIM, ALLEXCEPT(FallDetailDIM, FallDetailDIM[YrQtr], FallDetailDIM[MRN])),
FallDetailDIM[Index]
)
RETURN IF(FallDetailDIM[Index] = _minIndex, BLANK(), _minIndex)
Hi @cathoms,
Probably not the best formula for that case but seems to work 😉
Result =
VAR _minIndex =
MINX(
CALCULATETABLE(FallDetailDIM, ALLEXCEPT(FallDetailDIM, FallDetailDIM[YrQtr], FallDetailDIM[MRN])),
FallDetailDIM[Index]
)
RETURN IF(FallDetailDIM[Index] = _minIndex, BLANK(), _minIndex)
Best formula or not that did the trick! Thanks!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |