I have an issue wbile using RELATED() function to create a measure.

[SDVN]

Subject is the base table which contain unique subject names. This tabel is connected to different tables like "_common_ae_sif_signature_state", "_common_edc_query_status", "_common_form_raw". Now I want to get the distinct count of subject numbers based on the specified conditions. Can you help?

I have written: 

Measure =

CALCULATE(

    DISTINCTCOUNT(Subject[subject_number]),

    RELATED(_common_ae_sif_signature_state[form_signed]) = "No" ||

    RELATED(_common_casebook_signature_lock[cb_locked]) = "No" ||

    RELATED(_common_edc_query_status[query_state_name]) IN {"Open", "Answered"} ||

    RELATED(_common_form_raw[sdvcompletestate]) = "No" && RELATED(_common_form_raw[hassdvrequireditems]) = "Yes"

)

But I am geeting an error saying "The column '_common_ae_sif_signature_state[form_signed]' either doesn't exist or doesn't have a relationship to any table available in the current context."

I checked the name of the column and its correct and I checked the model view and its the subject table is connected to all the used tables. 

[rohit1991]

hi @SDVN ,

Your issue is likely due to RELATED() only working when there is a one-to-one or many-to-one relationship from the base table to the related table. If the relationship is one-to-many, you should use RELATEDTABLE() instead.

Fix: Use FILTER Instead of RELATED

Try modifying your measure using FILTER() and LOOKUPVALUE() instead of RELATED():

Measure =
CALCULATE(
    DISTINCTCOUNT(Subject[subject_number]),
    OR(
        LOOKUPVALUE(_common_ae_sif_signature_state[form_signed], _common_ae_sif_signature_state[subject_id], Subject[subject_id]) = "No",
        LOOKUPVALUE(_common_casebook_signature_lock[cb_locked], _common_casebook_signature_lock[subject_id], Subject[subject_id]) = "No",
        LOOKUPVALUE(_common_edc_query_status[query_state_name], _common_edc_query_status[subject_id], Subject[subject_id]) IN {"Open", "Answered"},
        AND(
            LOOKUPVALUE(_common_form_raw[sdvcompletestate], _common_form_raw[subject_id], Subject[subject_id]) = "No",
            LOOKUPVALUE(_common_form_raw[hassdvrequireditems], _common_form_raw[subject_id], Subject[subject_id]) = "Yes"
        )
    )
)

[SDVN]

Again the error 😞
"Too many arguments were passed to the OR function. The maximum argument count for the function is 2."

[SDVN]

[johnt75]

RELATED requires a row context, which you don't have in this calculation. 

For this calculation you only need to place the filters on the required columns, so you can use

MEASURE =
CALCULATE (
    DISTINCTCOUNT ( Subject[subject_number] ),
    _common_ae_sif_signature_state[form_signed] = "No"
        || _common_casebook_signature_lock[cb_locked] = "No"
        || _common_edc_query_status[query_state_name]
        IN { "Open", "Answered" }
            || _common_form_raw[sdvcompletestate] = "No"
            && _common_form_raw[hassdvrequireditems] = "Yes"
)

[SDVN]

I got this error sir, "The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

[SDVN]

[bhanu_gautam]

@SDVN , Try using this alternative approach

Measure =
CALCULATE(
DISTINCTCOUNT(Subject[subject_number]),
FILTER(
RELATEDTABLE(_common_ae_sif_signature_state),
_common_ae_sif_signature_state[form_signed] = "No"
) ||
FILTER(
RELATEDTABLE(_common_casebook_signature_lock),
_common_casebook_signature_lock[cb_locked] = "No"
) ||
FILTER(
RELATEDTABLE(_common_edc_query_status),
_common_edc_query_status[query_state_name] IN {"Open", "Answered"}
) ||
FILTER(
RELATEDTABLE(_common_form_raw),
_common_form_raw[sdvcompletestate] = "No" && _common_form_raw[hassdvrequireditems] = "Yes"
)
)

[SDVN]

I got the error again! 
"A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."