Forum Discussion

JoeCrozier's avatar
JoeCrozier
Icon for Helper II rankHelper II
2 years ago
Solved

Display first chronological result from Power Query in Power bi without filtering

I have data like this: That I'd like to incorporate into this table on a dashboard:   Specifically I'd like to create "IRB of Record" which is the first chronological entry per study f...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi JoeCrozier 

    You can refer to the following solution.

    Sample data 

    1.Create a rank column in table

    Rank = RANKX(FILTER('Table',[PROTOCOL_NO]=EARLIER('Table'[PROTOCOL_NO])&&[Type_]=EARLIER('Table'[Type_])),[ACTION_DATE],,ASC)

    2.Then create a measure

    Measure = CALCULATE(MAX('Table'[IRB_COMMITTEE]),'Table'[Type_]="CIRB",'Table'[Rank]=1,'Table'[PROTOCOL_NO] IN VALUES('Table'[PROTOCOL_NO]),REMOVEFILTERS())

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi JoeCrozier 

You can refer to the following solution.

Sample data 

1.Create a rank column in table

Rank = RANKX(FILTER('Table',[PROTOCOL_NO]=EARLIER('Table'[PROTOCOL_NO])&&[Type_]=EARLIER('Table'[Type_])),[ACTION_DATE],,ASC)

2.Then create a measure

Measure = CALCULATE(MAX('Table'[IRB_COMMITTEE]),'Table'[Type_]="CIRB",'Table'[Rank]=1,'Table'[PROTOCOL_NO] IN VALUES('Table'[PROTOCOL_NO]),REMOVEFILTERS())

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.