Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
cathoms
Helper V
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:

YearQuarterMonthYrQtrFileIDMRNIndexDesired Output
20231320230140091234198 
20231320230140921234203198
20231320230143851234224198
2023242023025659123427 
202324202302572312343027
202324202302595112344827
202324202302595312345027
202324202302621612345927

 

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
wini_R
Resolver IV
Resolver IV

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)

wini_R_0-1708553967629.png

 

View solution in original post

2 REPLIES 2
wini_R
Resolver IV
Resolver IV

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)

wini_R_0-1708553967629.png

 

Best formula or not that did the trick! Thanks!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.