Using LOOKUPVALUE in DAX measure

Question

I am trying to achieve something that I believe should not be so hard, but it will not work.

Until now we had a measure that calculated how many jobs had been 'created' and 'executed' in the same month. We did this by using the date-id's:

CALCULATE('ia FactConv'[Sum of Jobs], LEFT('ia FactConv'[CreationDateId],6) = LEFT('ia FactConv'[ExecutedDateId],6))

But now we don't want to check on calendar months but on bookmonths. So a bookmonth could be from 29th till 28th or from 3rd to 1st of the next month etc. And then the above solution will not work anymore.

Next to the FactConv we also have a couple of date dimensions and these also contain a column with the bookmonth per unique date. So I was guessing something like this should work:

VAR CreationBookMonth = LOOKUPVALUE('ia DimCreaDate'[BookMonthNumber],'ia DimCreaDate'[DateId],'ia FactConv'[CreationDateId])

VAR ExecutionBookMonth = LOOKUPVALUE('ia DimExecDate'[BookMonthNumber],'ia DimExecDate'[DateId],'ia FactConv'[ExecutedDateId])

VAR Result = CALCULATE('ia FactConv'[Sum of Jobs], CreationBookMonth = ExecutionBookMonth)

Return Result

But it keeps giving errors on the search_value (the last argument of the LOOKUPVALUE). The documentation states that it should/could be a column, but in Visual Studio I am getting the suggestion that it should be measure. And when I continue anyway I get the message 'A single value for CreationDateId in FactConv can not 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.'

So I have a couple of questions:

1. Why cant I use the LOOKUPVALUE as I expected?
2. What should I adjust to use it and make the above function work?
3. Is there any other (easier) way to achieve what I would like?

Answer 1

In a Measure, you don't get a single row value of a column, you get the whole column, hence the error you are getting. To use LOOKUPVALUE in a measure, you will need to get a singular row value with either MIN, MAX, SELECTEDVALUE etc... This won't help you for your scenario.

You have a few options.
1. Create calculated columns in your 'ia FactConv' table with:

CreationBookMonth =
  LOOKUPVALUE('ia DimCreaDate'[BookMonthNumber],'ia DimCreaDate'[DateId],'ia FactConv'[CreationDateId])

ExecutionBookMonth =
  LOOKUPVALUE('ia DimExecDate'[BookMonthNumber],'ia DimExecDate'[DateId],'ia FactConv'[ExecutedDateId])

Then your measure would simply be:

MyMeasure = 
  VAR Result = CALCULATE('ia FactConv'[Sum of Jobs], 'ia FactConv'[CreationBookMonth] = 'ia FactConv'[ExecutionBookMonth])
  Return Result

2. Use Relationships
Create two relationships:

'ia DimCreaDate'[DateId] one-to-many 'ia FactConv'[CreationDateId]`
'ia DimCreaDate'[DateId] one-to-many 'ia FactConv'[ExecutedDateId]`

One of these will have to be Inactive (both could also be Inactive).

Then your measure could look like:

MyMeasure = 
  var creationIds =
    CALCULATETABLE(
      DISTINCT('ia FactConv'[Job ID]),
      USERELATIONSHIP('ia DimCreaDate'[DateId], 'ia FactConv'[CreationDateId])
    )

  var executedIds =
    CALCULATETABLE(
      DISTINCT('ia FactConv'[Job ID]),
      USERELATIONSHIP('ia DimCreaDate'[DateId], 'ia FactConv'[ExecutedDateId])
    )

  RETURN
    COUNTROWS(
      INTERSECT(creationIds, executedIds)
    )

As per comments below, option 2 doesn't do same bookmonth, it will work if you are using bookmonth in a slicer or visual dimension. Try option 3 below.

3. Measure with SUMX

MyMeasure = 
  SUMX(
    'ia FactConv',
    (
      var cBM = LOOKUPVALUE('ia DimCreaDate'[BookMonthNumber],'ia DimCreaDate'[DateId],'ia FactConv'[CreationDateId])
      var eBM = LOOKUPVALUE('ia DimCreaDate'[BookMonthNumber],'ia DimCreaDate'[DateId],'ia FactConv'[ExecutedDateId])

      return IF(NOT ISBLANK(cBM) && cBM = eBM, 1)
    )
  )

This is assuming that you have one Job per row in the table.