Can't create a measure by using two columns name from different tables for a sanity check

[Anonymous]

Hi all,

I want to create a measure which checks, for each row, if value in Table1.column A = Table2.Column B.

The problem is, if I try to use an IF statement in the fields of table1, I can't mention any of column A /B ! For instance, here I want to select the column EMEA which is in table 1, but for some reason, such column is not detected. 

These are the relationship between the table

Any help?

[Anonymous · Accepted solution]

With the concept of Murtaza, I have created the below formula which works. I needed to use RELATED function otherwise I wasn't able to pick the column in the other table for some reason.

SanityCheck_EMEA = IF('Connectivity Coverage analysis xlsx_https://iontradingcom sharepoint com/teams/F'[EMEA ] = RELATED(components_mkt[europe]) && 'Connectivity Coverage analysis xlsx_https://iontradingcom sharepoint com/teams/F'[EMEA ]="yes",1,0)

[MURTAZA]

Hi @Anonymous 

You can only create a measure with scalar values. What you are trying to do in your example is refer to a column, which is not possible in a measure as it has to be a scalar value, however, it can be done using a calculated column at the many side table of the relationship.

If you want to create a measure, what you need to do is use SELECTEDVALUE nested in IF. SELECTEDVALUE will convert your table to a scalar. Try something like this:
=IF (SELECTEDVALUE(Table1.column A) = SELECTEDVALUE(Table2.column B)),1,0)

[Anonymous]

EDIT

Thanks! But now there is an issue, I need to confirm the fields in both columns are actually = Yes, because if they are both empty this formula says true but that's a false positive. How to fix this? I tried using AND table1, table2 = yes but is not working (if this helps, one column has written yes while the other Yes )

Also, why do I need to use selected value? I noticed that if I don't, I can't find the table2.ColumnB

[MURTAZA]

@Anonymous 
There can be different approaches to handle blanks, You may use IF nested within IF or AND nested within IF, etc. See an example below:

=IF (

    SELECTEDVALUE(Table1.column A) = SELECTEDVALUE(Table2.column B)

    &&

     SELECTEDVALUE(Table1.column A) <> Blank() ,

     "Yes",

      "No")

If this doesn't solve your problem, feel free to share a sample file.

For SELECTEDVALUE concept, I would suggest reading Microsoft documentation on Measure and Calculated Columns Concepts. In short, a measure should return a scalar value (single value), if no filters are passed. When we use a measure in a visual with a category or other fields, filters are passed through the measure. Referencing an entire column is a row-level operation and can only be done in calculated columns (just like you would do in excel). SELECTEDVALUE translates as IF (HASONEVALUE), this means DAX engine considers this as a scalar value.

[Anonymous]

hello @Anonymous ,

if function cannot work with columns directly in measure.

to use if in measure with columns used in calculate and filter function.