Create a column with filters

[Vally]

Good day experts , am new to dax queries.

am trying to create a new column which will illustrate the number of employees that have an action rate on more than 85% and conversion rate of more than 23% by name and segment. i have created the below dax queiry however it return the error below.

Spoiler

test_code = FILTER(BI_BANKER,BI_BANKER[AE_Key]  &&

FILTER(BI_CONVERSATION ,BI_CONVERSATION[(C) 12M Action Rate] > 0.83 &&  FILTER(BI_CONVERSION,BI_CONVERSION[(C) 12M Conversion Rate] > 0.22 && TRUE() && FALSE() ))

)

[Vally]

i re-ran code and the below error message occur.

[123abc · Accepted solution]

Plz try this:

test_code =
CALCULATE(
COUNTROWS(BI_BANKER),
FILTER(
BI_BANKER,
BI_BANKER[AE_Key] IN
FILTER(
BI_CONVERSATION,
BI_CONVERSATION[(C) 12M Action Rate] > 0.85
) &&
BI_BANKER[AE_Key] IN
FILTER(
BI_CONVERSION,
BI_CONVERSION[(C) 12M Conversion Rate] > 0.23
)
)
)

In this corrected DAX formula:

1. We use the CALCULATE function to perform calculations within a filter context.
2. We use COUNTROWS(BI_BANKER) to count the rows in the BI_BANKER table that meet the specified conditions.

The FILTER function is used to filter rows in the BI_BANKER table based on the conditions you mentioned. We check if the AE_Key is in the filtered list of AE_Key values from both the BI_CONVERSATION and BI_CONVERSION tables, where the action rate and conversion rate conditions are met.

This DAX query should give you the count of employees who have an action rate above 85% and a conversion rate above 23%. Make sure to replace the table and column names with the actual names in your data model. 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

It looks like you're trying to create a new column in a table based on certain conditions involving multiple tables. The DAX query you provided has some syntax issues. You can use the FILTER and SUMX functions to achieve your goal. Here's a corrected DAX query:

test_code =
SUMX(
FILTER(
BI_BANKER,
CALCULATE(MAX(BI_CONVERSATION[(C) 12M Action Rate])) > 0.85 &&
CALCULATE(MAX(BI_CONVERSION[(C) 12M Conversion Rate])) > 0.23
),
1
)

Here's what this DAX query does:

1. We use the FILTER function to filter the BI_BANKER table based on the conditions you specified.
2. The CALCULATE function is used to get the maximum value of the Action Rate and Conversion Rate for the filtered rows.
3. We check if the Action Rate is greater than 0.85 and the Conversion Rate is greater than 0.23.
4. The SUMX function is used to count the number of rows that meet the specified conditions. We add 1 for each row that meets the conditions.

This should create a new column test_code in your table that counts the number of employees who have an Action Rate over 85% and a Conversion Rate over 23% by name and segment.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.