Forum Discussion

mazsheps's avatar
mazsheps
New Member
6 years ago
Solved

Filter or grouping help

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.

 

NameProd DateStatusLots more columns
A1/1/17Final 
A1/1/17Preliminary 
B1/1/17Final 
B1/1/17Preliminary 
A2/1/17Final 
A2/1/17Preliminary 
B2/1/17Final 
B2/1/17Preliminary 
A3/1/17Preliminary 
B3/1/17Preliminary 

 

to give 

 

NameProd DateStatusLots more columns
A1/1/17Final 
B1/1/17Final 
A2/1/17Final 
B2/1/17Final 
A3/1/17Preliminary 
B3/1/17Preliminary 

 

Any help would be hugely appreciated.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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