Only show rows that have certain criteria for a given day

[Warren_FDA]

I have a jobs table that shows all the positions in an office for every single day of the year.  I want to report the status of the positions on the last day of the month (in this case, 2/29/2024):

Sometimes a Position ID has two statuses on a given day. Only one of them is the correct one to report out.

I have used a FILTER function to filter for only 2/28/2024 and office LNO.  For Positions GOLN10 and GOLN11, there are two statuses, based on the fields [Status] and [Sub-Status].  I need to report the name that matches with the correct Status/Sub-Status pair, based on the rank above.  The end result is a table where there is only one row per Position ID.

If any other information is needed, please let me know.  Many thanks!

[Anonymous · Accepted solution]

Hi @Warren_FDA ,

You can try formula like below:

result_ =
VAR string =
    SELECTCOLUMNS (
        'FindTable',
        "a",
            'FindTable'[Status] & " " & 'FindTable'[Sub-Status]
    )
RETURN
    IF (
        MAX ( 'Table'[Pos ID #] ) IN { "GOLN10", "GOLN11", "GOLN14" },
        CALCULATE (
            MAX ( 'Table'[Status] ),
            FILTER ( 'Table', MAX ( 'Table'[Sting_] ) IN string )
        ),
        MAX ( 'Table'[Status] )
    )

Sting_ = 'Table'[Status] & " " &'Table'[Sub-Status]

Best Regards,
Adamk Kong

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

[Warren_FDA]

Good morning  v-kongfanf-msft -

Your solution works!  Your concatenating of the [Status] and [Sub-Status] fields, along with using the MAX function not only solved this problem, but was the infrastructure needed for another task.  Thank you for your input and breaking the mental logjam.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Warren_FDA]

Thank you very much for the right direction, Ibendlin.  Question: should I delete this question and repost it anew with the tables I'm currently making?  Regards.

[lbendlin]

Please continue here.  Include a link to the usable sample data.

[Warren_FDA]

I am going to restate my question in brevity here:  I have a list of all the positions for an office on a certain day.  Each position is identified by a Position ID:

Office	Pos ID #	Status	Sub-Status	Name	Date
LNO	GOLN1	Vacant	Detail in	ROGERS, ROY	2/29/2024
LNO	GOLN10	Vacant	EOD Pending	GAYE, MARVIN	2/29/2024
LNO	GOLN10	Vacant	TDY	GAYE, MARVIN-1	2/29/2024
LNO	GOLN11	Vacant	Detail in	DaCAT, FELIX	2/29/2024
LNO	GOLN11	Vacant	Vacant		2/29/2024
LNO	GOLN12	Filled	At Post	GRIFFITH, NANCY	2/29/2024
LNO	GOLN13	Vacant	Vacant		2/29/2024
LNO	GOLN14	Filled	Detail out	BELLAMY, MATT	2/29/2024
LNO	GOLN14	Filled	Detail in	WOLSTENHOLME, CHRIS	2/29/2024
LNO	GOLN15	Vacant	Vacant		2/29/2024
LNO	GOLN16	Vacant	Vacant		2/29/2024
LNO	GOLN17	Filled	At Post	MERRITT, STEPHIN	2/29/2024

Notice that Position ID's GOLN10, GOLN11 and GOLN 14 have two rows each, each with a unique status/sub-status pair and a name of a person.  When there are two rows, only the row that has one of the following four pairs will be chosen.  It is guaranteed one of the following four pairs will be one of the rows:

Status	Sub-Status
Filled	At Post
Filled	Detail out
Vacant	EOD Pending
Vacant	Vacant

I need to write the dax that will output the following table:

Office	Pos ID #	Status	Sub-Status	Name	Date
LNO	GOLN1	Vacant	Detail in	ROGERS, ROY	2/29/2024
LNO	GOLN10	Vacant	EOD Pending	GAYE, MARVIN	2/29/2024
LNO	GOLN11	Vacant	Vacant		2/29/2024
LNO	GOLN12	Filled	At Post	GRIFFITH, NANCY	2/29/2024
LNO	GOLN13	Vacant	Vacant		2/29/2024
LNO	GOLN14	Filled	Detail out	BELLAMY, MATT	2/29/2024
LNO	GOLN15	Vacant	Vacant		2/29/2024
LNO	GOLN16	Vacant	Vacant		2/29/2024
LNO	GOLN17	Filled	At Post	MERRITT, STEPHIN	2/29/2024

My apologies, but my company does not allow us to attach to external cloud storage, e.g., Dropbox.