Skip to main content
cancel
Showing results for 
Search instead 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.

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 II
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)

wini_R_0-1708553967629.png

 

View solution in original post

2 REPLIES 2
wini_R
Resolver II
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)

wini_R_0-1708553967629.png

 

Best formula or not that did the trick! Thanks!

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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