Returning a cell in a row where row is the last true in an order of steps

[JPOso]

Hi PowerBI community!

I just recently registered to the community and am a newbie at powerBI, I would be ever so grateful for the community's help on this. I work in clinical trials and R&D for my trust and am currently doing this project for my workplace and apprenticeship but have been stuck in trying to find a solution (online, self experimentation on powerBI, etc).

So what I want to achieve is to return the comments cell on a row that is the last 'True', the challenge is there are a set of steps in the 'Order' column that are recurring (1-16) this repeats for each different trial. I've also set an index column to not lose the order of each trial if sorted. I have tried experimenting with similar solutions I've come across here but I can't get them to work.

What I want the end user to achieve is when they click on a specific trial(in the visual solution) it should return the last comment entered which is on the same row as the last 'True' cell.

I hope I am not overcomplicating this. Thank you once a gain for your help.

Edit:

usable format

Project Short Title	Order	Task	Question	Is Completed	Date Completed	Comments	Index
Trial1	1	Date site selected	00 When was the site selected?	TRUE	04/09/2024	comment	1
Trial1	2	Review Principal Investigator (PI) assigned	07 Who is the PI?	TRUE	04/09/2024	comment	2
Trial1	3	Expression of interest (EOI)	07 Has the Expression Of Interest been filled and submitted	TRUE	04/09/2024	comment	3
Trial1	4	Request information pack	07 Has the Local Information Pack been requested?	TRUE	25/09/2024	comment	4
Trial1	5	Put on R&D committee agenda	14 Has this been put on the agenda? Has the PI been invited?	TRUE	15/09/2024	comment	5
Trial1	6	review of Local Information Pack (LIP)	14 Review of feasibility to open the study	TRUE	25/02/2025	comment	6
Trial1	7	Feasibility meeting	14 Is the PI/ research coordinator happy with the protocol and support? review eligibility criteria and end of recruitment date. How many people with eligible criteria do they see? Where do they What is their target recruitment?	TRUE	25/02/2025	comment	7
Trial1	8	recruitment target setting	14 What is the recruitment target	TRUE	18/10/2024	comment	8
Trial1	9	review of costings for the study	14 What are the procedures that need to be done, are they standard of care?	TRUE	25/02/2025	comment	9
Trial1	10	Complete Organisational Information Document (OID) / contracting documents	14 Complete OID putting in the information for the Trust, finance and PI	TRUE	26/02/2025	comment	10
Trial1	11	Pharmacy review	14 Is pharmacy involved?	TRUE	04/09/2024	comment	11
Trial1	12	Pathology support	14 Is there a research cost with pathology?	TRUE	04/09/2024	comment	12
Trial1	13	Radiology review	14 Does the study need radiology support?	TRUE	04/09/2024	comment	13
Trial1	14	Has an SIV been organised?	30 Has a SIV been organised?	FALSE			14
Trial1	15	Issue Capability & Capacity for the study	35 Issue C&C	FALSE			15
Trial1	16	Trial site issues Green light to start recruitment	40 Has the trial site issued a green light	FALSE			16
Trial2	1	Date site selected	00 When was the site selected?	TRUE	29/07/2024	comment	17
Trial2	2	Review Principal Investigator (PI) assigned	07 Who is the PI?	TRUE	29/07/2024	comment	18
Trial2	3	Expression of interest (EOI)	07 Has the Expression Of Interest been filled and submitted	TRUE	03/12/2024	comment	19
Trial2	4	Request information pack	07 Has the Local Information Pack been requested?	TRUE	23/12/2024	comment	20
Trial2	5	Put on R&D committee agenda	14 Has this been put on the agenda? Has the PI been invited?	TRUE	04/11/2024	comment	21
Trial2	6	review of Local Information Pack (LIP)	14 Review of feasibility to open the study	TRUE	13/01/2025	comment	22
Trial2	7	Feasibility meeting	14 Is the PI/ research coordinator happy with the protocol and support? review eligibility criteria and end of recruitment date. How many people with eligible criteria do they see? Where do they What is their target recruitment?	FALSE			23
Trial2	8	recruitment target setting	14 What is the recruitment target	FALSE			24
Trial2	9	review of costings for the study	14 What are the procedures that need to be done, are they standard of care?	TRUE	15/01/2025	comment	25
Trial2	10	Complete Organisational Information Document (OID) / contracting documents	14 Complete OID putting in the information for the Trust, finance and PI	FALSE			26
Trial2	11	Pharmacy review	14 Is pharmacy involved?	TRUE	29/07/2024	comment	27
Trial2	12	Pathology support	14 Is there a research cost with pathology?	TRUE	27/07/2024	comment	28
Trial2	13	Radiology review	14 Does the study need radiology support?	TRUE	29/07/2024	comment	29
Trial2	14	Has an SIV been organised?	30 Has a SIV been organised?	FALSE			30
Trial2	15	Issue Capability & Capacity for the study	35 Issue C&C	FALSE			31
Trial2	16	Trial site issues Green light to start recruitment	40 Has the trial site issued a green light	FALSE			32

 

 

[jgeddes · Accepted solution]

A measure like the following could work for you. 
Replace the 'Table' with your table name.

Measure = 
//filter table to only include 'TRUE' Is Completed
var _vTable = 
FILTER('Table', 'Table'[Is Completed] = TRUE())
//get the max order from the filtered 'True' table
var _maxOrder = 
MAXX(_vTable, [Order])
//return the comment where the Order value equals the maxOrder value.
var _result =
MAXX(FILTER(_vTable, [Order] = _maxOrder), [Comments])
RETURN
_result

[v-tsaipranay]

Hi @JPOso ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also thank you @jgeddes and @MasonMA and @FBergamaschi actively providing solutions.

I wanted to check if you had the opportunity to review the information provided by @MasonMA and @jgeddes. I hope that could resolve your issue. Please feel free to contact us if you have any further questions.

Thank you.

[JPOso]

Hi v-tsaipranay,

Thank you for the kind welcome.

The responses are promising but unfortunately can not test them out fully as I am away on leave. Please leave this open until I get to test the responses out. Thank you!

[MasonMA · Accepted solution]

Hi @JPOso 

One of the solutions i tried is create one Column with DAX below. This returns the Last Comment in the right row context. 

LastCompletedComment = 
VAR _trial = 'Table'[Project Short Title]
VAR _maxIndex = 
    CALCULATE(
        MAX('Table'[Index]),
        FILTER(
            'Table',
            'Table'[Project Short Title] = _trial &&
            'Table'[Is Completed] = "TRUE"
        )
    )
RETURN
    IF (
        'Table'[Index] = _maxIndex &&
        'Table'[Is Completed] = "TRUE",
        'Table'[Comments],
        BLANK()
    )

and then in reporting, add this colunm in the report and filter out the 'Blank' rows in report

[JPOso]

Hi MasonMA,

Thank you for your reply, and sorry for the late response. I'm replying out of the office right now and quickly tested this out at home as I'm currently on annual. 

It seems to work but it flags out an error saying it won't accept true/false and text values. Sorry I have limited access to test this out further but I wanna test it out some more

[MasonMA]

@JPOso 

Hi, this would be a common data type mismatch between a Boolean type and Text type. 

The easy fix would be to update the code as below. 

'Table'[Is Completed] = TRUE()

Hope it works:) 

[JPOso]

Hi MasonMA,

I just want to say Thank you very much for your help on this. It works! I will try to test this out extensively.

One concern I have is that if there is a 'False' in between the string of 'True'. It returns the last 'True' and not the 'True' before. Am I right saying there is no solution to this rather than to just clean the process that there shouldn't be a false in between the string of 'True'?

Once again I want to say, thank you!

[MasonMA]

Happy to see it works for you! 

For your question, If the business rule really is "no FALSE should appear before the last TRUE", you could also enforce this upstream in Power Query:)

[jgeddes · Accepted solution]

A measure like the following could work for you. 
Replace the 'Table' with your table name.

Measure = 
//filter table to only include 'TRUE' Is Completed
var _vTable = 
FILTER('Table', 'Table'[Is Completed] = TRUE())
//get the max order from the filtered 'True' table
var _maxOrder = 
MAXX(_vTable, [Order])
//return the comment where the Order value equals the maxOrder value.
var _result =
MAXX(FILTER(_vTable, [Order] = _maxOrder), [Comments])
RETURN
_result

[JPOso]

Hi jgeddes,

This works really great too! Thank you for this solution. May I pick your mind abit what are the pros and cons between just using a straight up measure than how MasonMA did it? Would it be just showing the whole working process within if someone is interested?

Thank you for your help!

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[JPOso]

Thank you, I have corrected it.