Get first value of a grouped/repeating index column

[cathoms]

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!

[wini_R · Accepted 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)

[cathoms]

Best formula or not that did the trick! Thanks!