Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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()))
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.
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