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 ,

Why RELATED() isn’t working: The RELATED function only works in calculated columns, and only when there’s a direct one-to-many relationship from your base table to the related table. If you’re trying to do this in a measure, or if your tables are joined many-to-many (or via an indirect relationship), RELATED can’t be used.

What you can do instead:

1. For direct lookups:
   Use LOOKUPVALUE in your measure. Example: StatusValue = LOOKUPVALUE(
   RelatedTable[Status],
   RelatedTable[Key], BaseTable[Key]
   )
   
   

2. For more complex filters: If you need to filter your measure based on multiple columns or conditions, use FILTER and CALCULATE, or TREATAS if tables don’t have direct relationships. Example:
   Measure =
   CALCULATE(
   DISTINCTCOUNT(BaseTable[subject_number]),
   FILTER(
   ALL(BaseTable),
   LOOKUPVALUE(RelatedTable1[ColA], RelatedTable1[Key], BaseTable[Key]) = "SomeValue"
   && LOOKUPVALUE(RelatedTable2[ColB], RelatedTable2[Key], BaseTable[Key]) = "AnotherValue"))
   ​

If your logic gets too complex, sometimes it’s easier to merge/join tables in Power Query first, so you only have to deal with a single table in your DAX. If you can share a small sample pbix file or a screenshot of your data model (with dummy data), I’m happy to take a look and help tweak the measure to your exact scenario!

[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."