Filters based on IDs

[atziovara]

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!

[hackcrr]

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.

[atziovara]

@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

[hackcrr]

Hi, @atziovara 

Thank you very much for your reply. I'm a little confused as to why the
CompanyID=1 is empty for the corresponding FilteredAnswer column?

Best Regards,

hackcrr

[atziovara]

@hackcrr The data for the company whose ID = 1 are empty, because I would like to perform calculations for all the other companies that fit the criteria. I want to filter out companies based on the criteria related to Future Demand and then perform calculations with the answers of the remaining companies.

[hackcrr · Accepted solution]

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.