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.

 

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

This worked great! Thank you! I accepted it as a solution but actually I have one question (that I hadnt included originally) thats related and maybe you could help with.   So this measure totally returns the correct values, problem is:  not every row HAS a value.  I.e. not every protocol has a CIRB row.  This is expected behavior and I'd love to just show those rows as blank on the dashboard.  Problem is, if theres no result in the measure for those rows, they just aren't included on the dashboard.   How can i still show the blank row?

 

That make sense?