Creating two tables with filters

[Anonymous]

Looking for some help with a filter. I'm trying to find a way to filter data into two tables to be able to compare them. In our warehouse we have jobs that run with the following events; MPICK, TOPOF, and MDOFF. Every job has the events MPICK and MDOFF. Only a portion of the jobs have all three. Is there a way to filter out the jobs that have all three events, remove them and place in their own table?

[jorge_olivares]

Hey @Anonymous !

I made this file with some sample data. To make the table without the values you need you just need to modify the code from the last calculated table!

Please mark it as a solution if it helps, good luck @Anonymous!

Main table:

GroupByJob calculated table

GroupByJob = 
ADDCOLUMNS(
    SUMMARIZE('Table', 'Table'[JOB]), 
        "Event", CALCULATE(CONCATENATEX(DISTINCT('Table'[EVENT]), 'Table'[EVENT], ", ")))

Contains calculated table

Contains = FILTER('GroupByJob', 
    SEARCH("MDOFF", 'GroupByJob'[Event], 1, BLANK()) && 
    SEARCH("MPICK", 'GroupByJob'[Event], 1, BLANK()) &&
    SEARCH("TOPOF", 'GroupByJob'[Event], 1, BLANK()))

[Anonymous]

Here is an example of the trable that includes all three events. So in this example i would want to remove Job 14525647 and Job 14525641 from this table and place them in their own.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can add a measure as below:

Count event of each Job = CALCULATE(COUNT('Table'[EVENT]),ALLEXCEPT('Table','Table'[DATE],'Table'[JOB]))

Then drag this measure to visual filter  pane to filter out these data.

Best Regards,
Community Support Team _ Eason