search values in a column with conditions from other columns

[vmadm]

Hi everyone,

I am starting to use power BI and I have a difficulty.

I have a table similar to this

code 1	code 2	state
1	A	Open
2	B	Reviewed
3	C	Reviewed
4	D	Reviewed
5	E	Reviewed
6	B	Closed
7	B	Open
8	A	Reviewed
9	C	Reviewed
10	A	Open
11	F	Closed

I need to get the values of code1 or the corresponding rows in which the following conditions are met:
- the state is reviewed
- There is no other row with the same code 2 where the status is different from revised

For the table above, the result should be

code 1	code 2	state
3	C	Reviewed
4	D	Reviewed
5	E	Reviewed
9	C	Reviewed

Or

Codes 1:  3,4,5,9

[ibarrau · Accepted solution]

Hi. I don't really get what you need to do but I think this might help. This can be solved with power query too, but you asked in dax forum so I will do it on dax.

You can add a calculated column to flag your codes2 that have a state different from "Reviewed" to let you know about this condition. Like this:

FlagColumn = 
IF (
    Tabla[code 2] IN ( 
        SELECTCOLUMNS(
            FILTER( Tabla; Tabla[state] <> "Reviewed" )
            ; "codi"; Tabla[code 2] 
        )
    );
    TRUE();
    FALSE()
)

With this flag you can just filter the table as you wish:

dax column falg to filter

This is not the better solution regarding performance, but I don't know what you will do with this.

Hope this helps,

Regards,

[vmadm]

Thanks,

This solved my problem,

Regards.