DAX Power BI - IF Statement between two dates

Question

I am attempting to create a calculation column IF statement between two dates in Power BI.

At the moment, I want it to look at the two dates (in two tables). If they match, return "True" and if not return "False".

On Time? = 
IF (
    TB1[C1]
        = TB2[C1],
    "Yes",
    "No"
)

The error I get is;

A single value for column 'C1' in table 'TB1' cannot be determined. 
This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or 
sum to get a single result.

A sample of data is:

TABLE A              TABLE B
02 September 2010    02 September 2010
02 September 2010    03 September 2010
30 August 2010       29 August 2010

What Syntax should I be using in order to get the results I want?

Thanks

James, since you're using that formula in a measure, there is no row context, i.e. it is not clear which row this is supposed to look at when comparing values from the C1 columns (hence the error message). See this article for further explanations. I would offer you an alternative, but I have to admit it is not really clear to me what you're even trying to achieve. Could you explain the problem in a bit more detail, please? — mthierba
Hi, I am trying to create a new column that looks at column A and B. It will then show True or False for each row in the new column dependent on whether the values A and B match. Thanks. — James
So this is a calculated column then, not a measure. If A and B are in fact two different tables, and say you wanted to create the On Time column in table A, you'll have to find the corresponding comparison row from table B. If there's no relationship between the tables defined in your model, you use the LOOKUPVALUE function by matching values from the current row in A against columns in B. Be aware, though, that this fails if it can't match only one unique value. — mthierba
@mthierba - Thanks. There is already a relationship between the two tables. I should have mentioned that I have changed it from a "Measure" to a calculated column (simply called Column in Power BI) - But I still cannot resolve this issue. Should I be using an IF statement? — James
If there's already a relationship, then this should do: IF(TB1[C1]=RELATED(TB2[C1]),"Yes","No"). Assuming, this is a calculated column in TB1. — mthierba

greggyb greggyb · Accepted Answer · 2015-12-16T17:38:12

As mthierba mentioned in a comment, you may be able to use the RELATED() function. It is important to note that RELATED() in Power Pivot only works when called from the table that is on the many side of the many-to-one relationship. Even if your data is logically one-to-one, Power Pivot understands only a many-to-one relationship and one of the tables will be treated as the many.

IF(
    TB1[C1] = RELATED( TB2[C1] )
    ,"Yes"
    ,"No"
)

This will only work if TB2 is the lookup table - the one side.

If TB2 is the many side, then the following would work (albeit in TB2):

IF(
    TB2[C1] = RELATED( TB1[C1] )
    ,"Yes"
    ,"No"
)

You could alternately use LOOKUPVALUE() if there is a single distinct value in TB2[C1] for any given TB1[C1].

LOOKUPVALUE(
    TB2[C1]    // This is the field from which we will return a result
    ,TB2[<search field>]    // We will match this field against our lookup criterion - likely the relation field
    ,<search criterion>    // This would likely be relation field's value in TB1
)

DAX Power BI - IF Statement between two dates

1 Answers