Return value from other table, when three the criteria are met

[Daniel_Fdrvc]

Hi all,

maybe some one can help me with dax code that return a value [Name] to a new column from table_A but with condition:

• that if [ID] from table_B and from table_A is same and
• [Sales date] from table B is between two dates from table_A (two separate columns Table_A[Start date] and Table_A[End date], then return exact [Name] from table A that is in same row as the dates that include the date from Table B

In other wise in table_A can be two or more rows with same [ID], but always there will be different dates in [Start_date] and [End_date] (its like periods). Sometimes can be that there will be no date in [End_date] column, for example:

ID	Start_date	End_date	Name
2276	2018-01-01	2018-04-15	Example
2276	2020-05-01	2023-12-31	Example_2
2276	2024-04-01		Example_3

which means that until the End_date appears there, it should always return Example_3. In other words, table_A have the intervals when [Name] Example, Example_2 or Example_3 should be. In all other cases should leave blank.

[Daniel_Fdrvc · Accepted solution]

ChatGPT do amazing things... i have copy-pasted same question in to it and it writed me a solution, that works exactly how i needed...

Name = 
VAR CurrentID = table_B[ID]
VAR CurrentSalesDate = table_B[Sales date]

RETURN
    IF(
        ISBLANK(CurrentID) || ISBLANK(CurrentSalesDate),
        BLANK(),  -- Return blank if either ID or Sales date is blank

        // Check if the Sales date falls within any period in table_A for the same ID
        VAR MatchingName =
            CALCULATE(
                MAX(table_A[Name]),
                table_A[ID] = CurrentID,
                table_A[Start date] <= CurrentSalesDate,
                OR(
                    ISBLANK(table_A[End date]),  -- Allow for no End date (open-ended period)
                    table_A[End date] >= CurrentSalesDate
                )
            )
        
        RETURN
            COALESCE(MatchingName, "")  // Return matching Name or blank if no match found
    )

[Daniel_Fdrvc]

I have tried multiple solutions, but everytime i have an error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."