filtering data based on multiple conditions in another column

[formerlyknownas]

I'd like for my Power BI report to only consider batches and related output where the process was complete.  In this case, it means Process Numbers 1 and 3 are preseent.  There are various reasons why it might not be complete, but for reporting purposes I only want to consider batches where process number 1 and 3 are present.

Here is a sample of the data.  In this example, I'd like for Power BI to only consider B100 since Process_No 1 and 3 are present.

I don't quite know where to start with this, so any tips on this are very much appreciated.

[v-eqin-msft · Accepted solution]

Hi @formerlyknownas ,

Please use the following formula to create a measure:

Measure =
VAR _str =
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( 'Table'[Process_No] ),
            ALLEXCEPT ( 'Table', 'Table'[Batch] )
        ),
        'Table'[Process_No],
        ","
    )
RETURN
    IF ( CONTAINSSTRING ( _str, "1" ) && CONTAINSSTRING ( _str, "3" ), 1, 0 )

And then apply it to filter pane like this:

Here is the pbix file.

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

[formerlyknownas]

Thanks @v-eqin-msft - your approach helped get me to a working solution

[formerlyknownas]

Thanks @amitchandak - I'm not quite following your suggestions as they seem to to sum output, which is something I don't need to do.  What I'd like to ultimately be able to do is have a list of the relevant, fully complete batches and reference the numbers like output and so on in my graphs.  In case it's helpful, I think I may have over simplified my data sample, as there are in fact steps 1-6 and I just need to ensure 1 and 6 are present. 

[amitchandak]

@formerlyknownas 

Use above measures of this measure with Batch. Mesure will filter Batch

countx(filter(summarize(filter(Table, Table[Process_No] in {1,3}), Table[Batch], "_1", distinctCOUNT(Table[Process_No])),[_1]>=2),[Batch])

The above measures can also be used, I closed parenthesis at the wrong place

sumx(filter(summarize(filter(Table, Table[Process_No] in {1,3}), Table[Batch], "_1", distinctCOUNT(Table[Process_No]), "_2",sum(Table[Outpur])),[_1]>=2),[_2])

I am creating a filter table with 1,3, Counting distinct and checking that is should >= 2 to filter batches that do not have both

[amitchandak]

@formerlyknownas , Try a measure like

measure =
var _tab = filter(summarize(filter(Table, Table[Process_No] in {1,3}, Table[Batch]), "_1", distinctCOUNT(Table[Process_No])),[_1]>=2)
return
calculate(sum(Table[output]), filter(Table, Table[Batch] in _tab))

or

sumx(filter(summarize(filter(Table, Table[Process_No] in {1,3}, Table[Batch]), "_1", distinctCOUNT(Table[Process_No]), "_2",sum(Table[Outpur])),[_1]>=2),[_2])