Filter or grouping help

[mazsheps]

Hello,

I am trying to filter with conditions and I'm struggling to work out how to manage.  I have names A and B, Dates and a status ranked Final>Interim>Preliminary, so I just want to keep the highest ranked status for each name and date.  There are also a lot of other columns to the right.  I have tried adding a column to give the status a value and then grouping the values, but I can only get name and date and a custom column with number related to the status.  This does give the correct result but I also need all the other columns to be visible.

Name	Prod Date	Status	Lots more columns
A	1/1/17	Final
A	1/1/17	Preliminary
B	1/1/17	Final
B	1/1/17	Preliminary
A	2/1/17	Final
A	2/1/17	Preliminary
B	2/1/17	Final
B	2/1/17	Preliminary
A	3/1/17	Preliminary
B	3/1/17	Preliminary

to give 

Name	Prod Date	Status	Lots more columns
A	1/1/17	Final
B	1/1/17	Final
A	2/1/17	Final
B	2/1/17	Final
A	3/1/17	Preliminary
B	3/1/17	Preliminary

Any help would be hugely appreciated.

[v-lili6-msft]

hi  @mazsheps 

For your case, you could try this simple way as below:

Step1:

Create a measure as below:

highest status = 
IF (
    MIN ( 'Table'[Status] )
        = CALCULATE (
            MIN ( 'Table'[Status] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Name] = MAX ( 'Table'[Name] )
                    && 'Table'[Prod Date] = MAX ( 'Table'[Prod Date] )
            )
        ),
    MIN ( 'Table'[Status] )
)

Step2:

Then you could use one of these two way to show the expected output.

1. Drag all the fields you want to show and this measure except [Status] field in a table visual.

2. Drag all the fields you want to show and [Status] field in a table visual, then drag this measure into table visual level filter and set filter is not blank

here is sample pbix file, please try it.

Regards,

Lin

[mazsheps · Accepted solution]

I have found a solution using https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/

@v-lili6-msft works for visuals, but I was wating all the sorting done in power query editor.

[amitchandak]

Take a min of status. in Any visual, that should to along with the first two columns.