cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

## Get first value of a grouped/repeating index column

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!

1 ACCEPTED SOLUTION
Resolver II

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)

2 REPLIES 2
Resolver II

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)

Helper V

Best formula or not that did the trick! Thanks!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors