Forum Discussion
Filtering a column based on a value from another column
Hi,
I am trying to filter my dataset based on certain conditions, consider the below example.
Lets say the below Table 1 is my dataset. what i am trying to do is:
If in the 'Company' column the value is ABZ then these name which appears against ABZ (Steve, Mike, Rad, Miller, Ricky) , should form my new dataset. (refer the next table below for expected output).
Table 1
| Company | Name | Dept. | Hours | Topic |
| ABZ | Steve | Electrical | 8 | Thesis |
| F&F | Baker | Electrical | 10 | Thesis |
| M&M | Steve | Electrical | 5 | Thesis |
| HVK | Mike | Electrical | 10 | Thesis |
| Lama | Nathan | Mechanical | 9 | Research |
| ABZ | Mike | Mechanical | 3 | Research |
| F&F | Collin | Mechanical | 4 | Research |
| M&M | Bain | Auto | 9 | Incubation |
| HVK | Ivy | Auto | 5 | Incubation |
| Lama | Clair | Auto | 2 | Incubation |
| ABZ | Rad | Auto | 9 | Incubation |
| F&F | Hashim | Robotics | 6 | Thesis |
| M&M | Faf | Robotics | 2 | Thesis |
| HVK | Rad | Robotics | 2 | Thesis |
| Lama | Devilliers | CS | 10 | Thesis |
| ABZ | Miller | CS | 6 | Research |
| F&F | Shane | CS | 4 | Research |
| M&M | Steve | CS | 8 | Research |
| HVK | Mathew | Chemical | 6 | Incubation |
| Lama | Rad | Chemical | 1 | Incubation |
| ABZ | Ricky | Chemical | 8 | Incubation |
| F&F | Fahim | Chemical | 10 | Incubation |
| M&M | Mike | Electronic | 4 | Research |
| HVK | Grace | Electronic | 1 | Research |
Table 2 (Expected Output / Filtered Data)
| Company | Name | Dept. | Hours | Topic |
| ABZ | Steve | Electrical | 8 | Thesis |
| M&M | Steve | Electrical | 5 | Thesis |
| HVK | Mike | Electrical | 10 | Thesis |
| ABZ | Mike | Mechanical | 3 | Research |
| ABZ | Rad | Auto | 9 | Incubation |
| HVK | Rad | Robotics | 2 | Thesis |
| ABZ | Miller | CS | 6 | Research |
| M&M | Steve | CS | 8 | Research |
| Lama | Rad | Chemical | 1 | Incubation |
| ABZ | Ricky | Chemical | 8 | Incubation |
| M&M | Mike | Electronic | 4 | Research |
You would notice that in the expected filtered data in Table 2, the common names which were there for ABZ have been filtered from Table 1.
I am not sure, if this would require me to create a new column or a simple measure can be used to do this filtering. However, any help is highly appreciated.
Thanks
Haha, what a fun requirement. Here's my take:
Create a disconnected lookup table containing the company names. This will be used to populate the variable _Company in the measure, without filering our data.
The _Company variabl collects all companies, that a specific worker is related to.
The result checks, if the selected Company from the Disconnected Lookup is present in the _Companies variable
EDIT:
A few more arrows for clarification:
4 Replies
Haha, what a fun requirement. Here's my take:
Create a disconnected lookup table containing the company names. This will be used to populate the variable _Company in the measure, without filering our data.
The _Company variabl collects all companies, that a specific worker is related to.
The result checks, if the selected Company from the Disconnected Lookup is present in the _Companies variable
EDIT:
A few more arrows for clarification:Hi anwarbi,
I received this in my mailbox, but can't see the comment here.Hi,
Thanks for your help.
While I am sure, this solution would work. I was curious, if we can add filter to the output table, just to select certain Companies.
So for e.g. in the current output, as per your table in power bi, we have ABZ, HVK, Lama and M&M as companies. From this table, would it be possible to select just HVK and M&M, by maybe adding a slicer?
Thanks. Really appreciate.
I I think adding the company field from the 'Table' would do the trick and filter the output.
So you would have both Companny from the 'table' and the 'disconnectedtable'. Not very elegant, but it might be neccesarry for your specifik requirement.
Appreciate your kudos 😊Hi,
yes, I realised that having two company field in the report would allow me to filter. I can't thank you enough, it was super helpful. 😊
Thanks
This solution does work on 'Table' visual but when I use 'Bar chart' visual to calculate lets say Average hours and count of names, then despite having the measure filter of 1 applied on the visual, it still calculates the total count of names.
I have attached the sample power bi file, basically I am looking to include the same data in Bar chart as in the table, e.g. In table there are 3 distinct count of names so I want to see the same count in Bar chart as well.
Thanks,
