Dates in between dates in another table with multiple if statements

[Anonymous]

Hello,

I'm trying to identify whether project effective date is in between two dates in another table, matching the country names. I'm attaching an example pbix (Dropbox) and detailing everything here. This is an example pbix, my original data has thousands of rows so if your calculation/measure/column doesn't lead into any results, that's because I picked some randomized data. Let me know if more clarification is needed.

• In Campaign Levels table I have countries, campaign start and end dates as well as campaign levels.
• In Effective Date table I have effective Date, location (countries again), and amount. I want to see the campaign level in this table, if effective date is between campaign start and end date and if the country names match. 

In my example, there is an effective date for Japan for May 5th, 2021 so the campaign level should show L3. Because, there is a campaign in Japan between 1st Jan to 3rd June. However, for the effective date 7th June, it should show blank, as there are no campaigns on that effective date for Japan. Here, the country name is important as they may be campaigns in other countries on that effective date but I'm not interested in those. Keep in mind that in both tables, country names may appear more than once so the relationship between the two tables is many-to-many, and merging two tables cause items appear multiple times, which breaks the whole exercise. 

Many thanks,

[Greg_Deckler · Accepted solution]

@Anonymous Seems like an application for LOOKUPVALUE Range quick measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430

[Anonymous]

Thanks @Greg_Deckler It works marvelously though I don't understand why. Therefore, I can't fulfill my follow-up needs, which is to create a filter with this LOOKUPVALUE Range and a card visualization with the count of Campaign levels. Can you elaborate on the measure so I might be able to create a filter? Currently, I can't put the measure to a filter.

[Greg_Deckler]

@Anonymous Well, here is an explanation:

LOOKUPVALUE Range = 
  VAR __Score = MAX('Table'[Score]) // Get the value in context you want within a range
  VAR __ID = MAX('Table'[ScaleID]) // Get a value in context that is some specific identifier
  VAR __Scale = 
    MAXX( //MAXX find the maximum value for the column across a table of values
      FILTER( //Filter reduces the rows in a table
        'Scales', // this is the table to filter
        'Scales'[ScaleID] = __ID && // this is the specific thing to filter on && is logical AND
          __SCore >= 'Scales'[From] && //This is the first part of the range
            __Score <= 'Scales'[To] // this is the second part of the range
      ),
      [Scale Result] // This is the column to find the maximum of in the filtered table
    )
RETURN
  __Scale

It sounds like what you might want is to have this measure. You could perhaps use in an ADDCOLUMNS function and do a COUNTROWS over it.

[Anonymous]

Thank you for the clarification @Greg_Deckler I understand it better. Though, after nearly 24 hours and multiple attempts, I couldn't achieve what I wanted to. Can I ask for your help with that as well? As mentioned, I'm trying to create a filter for the campaign levels and have a total count of these levels, including the times when there was an effective project date but no campaign (in other words blanks).

Many thanks in advance,