How can I FILTER a table with more than one repeat ID and their lowest VALUES

[Anonymous]

Hi,

I have 2 tables wich is conected by relationship.

The table1 and table 2 after conected:

Table 1                                                     Table 2

ID                       STATUS                         ID                     STATUS NR          STATUS

123123               NOT SALES                   123123              1                         NOT SALES

123123               SALES                           123123              2                          SALES

What I am trying to get is:

123123               NOT SALES                    123123              1                        NOT SALES

For each Status I have their code number, and it be to need filtered by their number the lowest.

Thanks,

[parry2k]

@Anonymous how these tables are connected? and can you explain your output again.

[Anonymous]

Hi @parry2k, 

Is not connected anymore, I made a merge at power query.

Table to get is:

ID                STATUS NR

123123                  1

It is filtered by their lowest status nr values.

Thanks.   

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Merge columns [ID] and [STATUS] in two tables,

then we get [Merged], [Merged2] columns,

create relationship between two tables based on these two columns

Then add these columns in a table visual on the report page, add a measure to the viusal level filter as below

Measure =
VAR LOW_NO =
    CALCULATE (
        MIN ( Table2[STATUS NR] ),
        FILTER ( ALLSELECTED ( Table2 ), Table2[ID] = MAX ( Table2[ID] ) )
    )
RETURN
    IF ( LOW_NO <> BLANK () && MAX ( Table2[STATUS NR] ) = LOW_NO, 1 )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft , Wow, thanks a lot for your time and attached file!!!