For a Given ID#, 1-N Sequence#, and Status Column, Return the Min of Seq. for Rows Where Status =""

[maibacherstr]

Hello, I hope the below table will help describe a new measure needed. Given repeating ID numbers and a 1-N Sequence assigned to each, we also have a Status column with several blanks. The new measure should return a Status shown in the new column at the far right. Basically, the formula must identify the minimum sequence # for a given ID where Status is blank.

@Greg_Deckler thanks for answering a similar question elsewhere, unfortunately it couldn't get me all the way on this one...

Thank you all in advance.

ID	Sequence	Status	// My Description //	Result In New Column
1	1	Approved	<--Rows with a Status are all set	Approved
1	2	Approved		Approved
1	3	Rejected		Rejected
2	1	Approved		Approved
2	2	Approved		Approved
2	3		<--Minimum Sequence of a given ID, and Status = (blank)	Should read "In Progress"
2	4		<--Not the min. Seq. for the given ID, and Status = (blank)	Should read "To Do"
3	1	Approved		Approved
3	2	Rejected		Rejected
4	1	Approved		Approved

[Russell-PBI · Accepted solution]

Is there anything different about your table compared with the table you originally posted? Perhaps more columns? If so, this slight change might work better:

Column = 
VAR MinSequence =
CALCULATE(
Min('Table'[Sequence])
 , ALLEXCEPT('Table','Table'[ID])
 , 'Table'[Status] = BLANK()
 )
RETURN
SWITCH(
TRUE
, 'Table'[Status] = BLANK()
 && 'Table'[Sequence] = MinSequence
, "In Progress"
, 'Table'[Status] = BLANK()
 && NOT 'Table'[Sequence] = MinSequence
, "To Do"
, 'Table'[Status]
)

For info, the previous formula was only ignoring Sequence when finding the minimum Sequence value across matching IDs. The second formula ignores everything but the ID (this part: ALLEXCEPT('Table','Table'[ID]))

[tackytechtom]

Hi @maibacherstr ,

Here might a way to do it:

Here is the code for the measure:

TomsStatusMeasure = 
VAR _minNotBlank =
CALCULATE ( 
    MINX ( Table, Table[Sequence] ),
    Table[Status] = BLANK(),
    ALLEXCEPT ( Table, Table[ID] )
) 
RETURN
SWITCH (
    TRUE(),
    MAXX ( Table, Table[Status] ) <> BLANK(), MAXX ( Table, Table[Status] ),
    MAXX ( Table, Table[Sequence] ) = _minNotBlank, "In Progress",
    "To Do"
) 

Let me know if it works for you! 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[maibacherstr]

Hi Tom, as opposed to a helper column, I'm shakier on measures (realizing that's what I asked for..) but trying to make it work. In its final format, the measure actually needs to produce whole numbers, which I've adapted in a numerical Status column, as well as in your measure, in both cases where Approved = 1; Rejected = 2; In Progress = 3; To Do = 4. (To confirm, the measure is formatted as a whole number).

Looking good so far, the measure is dropped into the conditional format of a table, intended to produce icons for each status. Unfortunately, this has only produced a handful of "To Do" icons, but nothing else shows.

I'm going to keep working at this, but if you have any suggestions I greatly appreciate your time and energy sharing. Thanks, - Dan

[tackytechtom · Accepted solution]

Hi @maibacherstr ,

I turned the measure into a calculated column. Result:

Here my code:

TomsStatusColumn = 
VAR _minNotBlank =
CALCULATE ( 
    MINX ( Table, Table[Sequence] ),
    Table[Status] = BLANK(),
    ALLEXCEPT ( Table, Table[ID] )
) 
RETURN
SWITCH (
    TRUE(),
    Table[Status] <> BLANK(), Table[Status],
    Table[Sequence] = _minNotBlank, "In Progress",
    "To Do"
)

 
Hope with this you get closer to a solution that works for you! 🙂

/Tom

https://www.tackytech.blog/

https://www.instagram.com/tackytechtom/

[maibacherstr]

Hi Tom, in my first go at this, I realized I added the row ID instead of the ID column relevant to my model. I opted for your Column formula which worked right away after I cut over to the correct field. 

Many thanks for sharing your expertise! 

[Russell-PBI]

Hi @maibacherstr, maybe a calculated column would serve?:

Column = 
VAR MinSequence =
 CALCULATE(
 Min('Table'[Sequence])
 , ALL('Table'[Sequence])
 , 'Table'[Status] = BLANK()
 )

RETURN
SWITCH(
TRUE
, 'Table'[Status] = BLANK()
 && 'Table'[Sequence] = MinSequence
, "In Progress"
, 'Table'[Status] = BLANK()
 && NOT 'Table'[Sequence] = MinSequence
, "To Do"
, 'Table'[Status]
)

EDIT: Tidied up with a VAR.

[maibacherstr]

Hi @Russell-PBI the formula looks great and I adapted the table and column names carefully, and doublechecked my comparison back to the dataset; but for each Blank() Status, the formula is only returning "In Progress." The breakout of the "To Do" Status is the critical purpose... that said, I'm continuing to try to work this out. If you can think of any suggestions, please send. I appreciate your time and energy very much.

[Russell-PBI · Accepted solution]

Is there anything different about your table compared with the table you originally posted? Perhaps more columns? If so, this slight change might work better:

Column = 
VAR MinSequence =
CALCULATE(
Min('Table'[Sequence])
 , ALLEXCEPT('Table','Table'[ID])
 , 'Table'[Status] = BLANK()
 )
RETURN
SWITCH(
TRUE
, 'Table'[Status] = BLANK()
 && 'Table'[Sequence] = MinSequence
, "In Progress"
, 'Table'[Status] = BLANK()
 && NOT 'Table'[Sequence] = MinSequence
, "To Do"
, 'Table'[Status]
)

For info, the previous formula was only ignoring Sequence when finding the minimum Sequence value across matching IDs. The second formula ignores everything but the ID (this part: ALLEXCEPT('Table','Table'[ID]))

[maibacherstr]

Hi @Russell-PBI I'm sure this formula works! Hopefully the subject line of this thread will help others find and learn from it. Many thanks for putting in time to help!  - Dan