Forum Discussion
Filters based on IDs
I have a table called 'TableBI' which stores the answers of multiple companies to multiple questions. It looks like this:
| Company ID | Question | Answer |
| 1 | Current Demand | 0.3 |
| 1 | Future Demand | 0.2 |
| 1 | Current Labor | 0.4 |
| 1 | Future Labor | 0.5 |
| 2 | Current Demand | 0.5 |
| 2 | Future Demand | 0.4 |
| 2 | Current Labor | 0.3 |
| 2 | Future Labor | 0.2 |
| 3 | Current Demand | 0.3 |
| 3 | Future Demand | 0.3 |
| 3 | Current Labor | 0.6 |
| 3 | Future Labor | 0.7 |
I need to perform some calculations with the answers based on specific criteria. One of these criteria is that the Future Demand of the companies needs to be > 0.2.
Therefore, I would like to create either a new filtered table or a new column that will include ALL the Answers of ONLY the companies for which when [Question] = Future Demand, [Answer] > 0.2.
It should look like this:
| Company ID | Question | Answer | Filtered Answer |
| 1 | Current Demand | 0.3 | |
| 1 | Future Demand | 0.2 | |
| 1 | Current Labor | 0.4 | |
| 1 | Future Labor | 0.5 | |
| 2 | Current Demand | 0.5 | 0.5 |
| 2 | Future Demand | 0.4 | 0.4 |
| 2 | Current Labor | 0.3 | 0.3 |
| 2 | Future Labor | 0.2 | 0.2 |
| 3 | Current Demand | 0.3 | 0.3 |
| 3 | Future Demand | 0.3 | 0.3 |
| 3 | Current Labor | 0.6 | 0.6 |
| 3 | Future Labor | 0.7 | 0.7 |
This is very important, so thank you very much in advance!
Hi, atziovara
Thank you very much for your reply. I have re-edited my DAX expression below:
AnswerFiltered = VAR _table = SUMMARIZE ( FILTER ( 'Table', 'Table'[Question] = "Future Demand" && 'Table'[Answer] > 0.2 ), 'Table'[Company ID] ) RETURN IF ( 'Table'[Company ID] IN _table, 'Table'[Answer] )The results are as follows:
Best Regards,
hackcrr
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
