topic Re: Filters based on IDs in DAX Commands and Tips

Filters based on IDs

atziovara — Fri, 10 May 2024 09:18:10 GMT

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!

Re: Filters based on IDs

hackcrr — Fri, 10 May 2024 12:40:59 GMT

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.

Re: Filters based on IDs

atziovara — Fri, 10 May 2024 13:21:49 GMT

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

Re: Filters based on IDs

hackcrr — Fri, 10 May 2024 13:32:28 GMT

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

Re: Filters based on IDs

atziovara — Fri, 10 May 2024 13:36:43 GMT

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.

Re: Filters based on IDs

hackcrr — Fri, 10 May 2024 13:43:46 GMT

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.