cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 III

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 III

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors