Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating two tables with filters

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?

  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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

  • jorge_olivares's avatar
    jorge_olivares
    Frequent Visitor
    4 years ago

    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()))