cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

Fabric certifications survey

Certification feedback opportunity for the community.

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