DAX Measure to calculate the relevant with 1 for matching condition and 0 for not matching condition

[Suhel_Ansari]

Hi All,

I want to create a DAX measure to calculate the Relevant with 1 if the condition satisfy and 0 if the condtion does not satisfy.

The data is coming as seen in the follwoing screenshot, The SELECTED_RECALC_MONTH is a disconnected Table as ssen in the folloinwg screenshot and the condition is as follows.

SELECTED_RECALC_MONTH = SELECTEDVALUE(RECALC_MONTHS[Recalc_month])

1. 'DIM_CUSTOMER'[Customer recalculation month] = "Undefined" &&  'DIM_CUSTOMER'[Customer recalculation second month] = "Undefined") ,

        FILTER('DIM_HIGHEST_PARENT','DIM_HIGHEST_PARENT'[Highest parent recalculation month] =  SELECTED_RECALC_MONTH || 'DIM_HIGHEST_PARENT'[Highest parent recalculation second month] =  SELECTED_RECALC_MONTH + 'DIM_CUSTOMER'[Customer recalculation month] = "Undefined" || 'DIM_CUSTOMER'[Customer recalculation second month] = "Undefined"
'DIM_HIGHEST_PARENT'[Highest parent recalculation month] = SELECTED_RECALC_MONTH || 'DIM_HIGHEST_PARENT'[Highest parent recalculation second month] = SELECTED_RECALC_MONTH

2. All other record should be 0

My measure 
Relevant =
VAR SELECTED_RECALC_MONTH = SELECTEDVALUE(RECALC_MONTHS[Recalc_month])
VAR Result =
COUNTROWS(
CALCULATETABLE(SUMMARIZECOLUMNS('DIM_CONTRACT'[Contract ID], 'DIM_CUSTOMER'[Customer recalculation month], 'DIM_CUSTOMER'[Customer recalculation second month], 'DIM_HIGHEST_PARENT'[Highest parent recalculation month],'DIM_HIGHEST_PARENT'[Highest parent recalculation second month], "CountContract_ID", CALCULATE(COUNTA('DIM_CONTRACT'[Contract ID]))) ,
FILTER('DIM_CUSTOMER','DIM_CUSTOMER'[Customer recalculation month] = "Undefined" && 'DIM_CUSTOMER'[Customer recalculation second month] = "Undefined") ,
FILTER('DIM_HIGHEST_PARENT','DIM_HIGHEST_PARENT'[Highest parent recalculation month] = SELECTED_RECALC_MONTH || 'DIM_HIGHEST_PARENT'[Highest parent recalculation second month] = SELECTED_RECALC_MONTH))) +
COUNTROWS(
CALCULATETABLE(SUMMARIZECOLUMNS('DIM_CONTRACT'[Contract ID], 'DIM_CUSTOMER'[Customer recalculation month], 'DIM_CUSTOMER'[Customer recalculation second month], 'DIM_HIGHEST_PARENT'[Highest parent recalculation month],'DIM_HIGHEST_PARENT'[Highest parent recalculation second month], "CountContract_ID", CALCULATE(COUNTA('DIM_CONTRACT'[Contract ID]))) ,
FILTER( 'DIM_CUSTOMER','DIM_CUSTOMER'[Customer recalculation month] <> "Undefined" || 'DIM_CUSTOMER'[Customer recalculation second month] <> "Undefined") ,
FILTER('DIM_CUSTOMER','DIM_CUSTOMER'[Customer recalculation month] = SELECTED_RECALC_MONTH || 'DIM_CUSTOMER'[Customer recalculation second month] = SELECTED_RECALC_MONTH)))
RETURN
Result

Please assit. Thanks 

[v-tejrama · Accepted solution]

Hi @Suhel_Ansari ,

The problem arises when the measure is built using SUMMARIZECOLUMNS or similar table construction logic, because this creates multiple CustomerParentContract combinations. As a result, the relevance check ends up running on duplicated rows instead of on each individual contract, which causes inconsistent 0/1 results. The better approach is to skip any intermediate summary tables and calculate the 'Relevant' logic directly at the contract level, leveraging the relationships in your model. Conceptually, a contract is considered relevant - 1 if either the customer's recalc months are both 'Undefined' and the parent's recalc month matches the selected month, or if the customer has a defined recalc month that matches the selected month. In all other cases, the result should be 0. By applying the logic directly to each contract and using RELATED() to pull in customer and parent fields, the measure becomes stable, accurate, and free from the duplication issues caused by summarization.

[Suhel_Ansari · Accepted solution]

Thank you All, I got the solution 

[v-tejrama]

Hi @Suhel_Ansari ,

It looks like the issue you are running into comes from the way the logic is being evaluated across multiple tables. When the measure relies on row counts and summarized tables, it starts to create duplicate combinations of customer and parent records, which makes the final result inconsistent. The calculation becomes much more predictable once you evaluate everything directly in the current context of each contract.

The simplest way to approach this in DAX is to retrieve the selected month from your disconnected slicer with SELECTEDVALUE and then capture the two customer recalc fields and the two highest parent recalc fields the same way. After that, the logic follows exactly the rules you described. You check whether both customer values are Undefined and both parent values match the selected month. If that path is satisfied, the result is set to 1. If not, you check the other branch where at least one customer value is defined and at least one of them matches the selected month. If that is true, the result is also 1.

When neither path applies, the measure returns 0. Keeping the logic at this level avoids the need for intermediate tables and ensures each contract is evaluated cleanly and consistently.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @Suhel_Ansari ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tejrama]

Hi @Suhel_Ansari ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[Suhel_Ansari]

Hi @v-tejrama , Thank you for the responce, I did tried your approch using the SELECTEDVALUE however due to the size of the data and the Measure using the SELECTEDVALUE is not optimzed following is the measure i have modified.

please assist me. Thanks

[v-tejrama · Accepted solution]

Hi @Suhel_Ansari ,

The problem arises when the measure is built using SUMMARIZECOLUMNS or similar table construction logic, because this creates multiple CustomerParentContract combinations. As a result, the relevance check ends up running on duplicated rows instead of on each individual contract, which causes inconsistent 0/1 results. The better approach is to skip any intermediate summary tables and calculate the 'Relevant' logic directly at the contract level, leveraging the relationships in your model. Conceptually, a contract is considered relevant - 1 if either the customer's recalc months are both 'Undefined' and the parent's recalc month matches the selected month, or if the customer has a defined recalc month that matches the selected month. In all other cases, the result should be 0. By applying the logic directly to each contract and using RELATED() to pull in customer and parent fields, the measure becomes stable, accurate, and free from the duplication issues caused by summarization.

[v-tejrama]

Hi @Suhel_Ansari ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[Suhel_Ansari]

Hi Please help 
@ajaybabuinturi , @amitchandak, @uzuntasgokberk