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.
5 Replies
Hi, atziovara
You can try the following DAX expressions:
AnswerFiltered = IF('Table'[Question]="Future Demand"&&'Table'[Answer]>0.2,'Table'[Answer])This will filter out all answers with [Question] = Future Demand, [Answer] > 0.2. The results are as follows:
Best Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hackcrr Thank you for your answer!
I am afraid that your response does not resolve my issue. As stated above, I would like my new column to include all answers of companies for which when [Question] = Future Demand, [Answer] > 0.2. Not just the answers to Future Demand where the value is > 0.2
It should look like this:CompanyID Question Answer FilteredAnswer 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
