Obtain Date ID Column for Orders between start & end date

[nnouchi]

Hi there,

I'm trying to figure out how to reference a date and orders table that would give me an ID for the date an order was completed. I have set up a dates table that can be referenced by it's ID, however, cannot figure out how to bypass the message: " ...Table of multiple vaules was supplied when a single value was expected."

 Above I'm trying to reference this table with another one that has all of my order information.

CALCULATE (
    VALUES ( Dates[ID] ),
    FILTER (
        'Dates',
        'Dates'[Start_date] <= 'Orders'[Ordered_date] 
            && 'Dates'[End_date] >= 'Calendar'[Ordered_Date] 
    )
)

 Any help would be greatly appreciated, thanks!

[leandroab1]

You can just create a column "ID" using the date as the source. Then you can order all columns using this ID.

ID = VALUE(dim_calendar[Date])

[v-shex-msft]

HI @nnouchi,

I found you're try to looping date table to find out suitable records based on two different table columns, can you please explain more about these table structure? Of will be help for coding formula.

In addition, you can try to use below measure formula to find out suitable records:

Measure =
VAR orderDate =
    SELECTEDVALUE ( 'Orders'[Ordered_date] )
VAR calenadrDate =
    SELECTEDVALUE ( 'Calendar'[Ordered_Date] )
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Dates[ID] ), [ID], "," ),
        FILTER (
            ALLSELECTED ( 'Dates' ),
            'Dates'[Start_date] <= orderDate
                && 'Dates'[End_date] >= calenadrDate
        )
    )

Regards,

Xiaoxin Sheng

[nnouchi]

I'm not looking for a measure, I want to create a calculated column. I have a dates table with this structure.

 This ties back to a Orders table that is structured like the following (cannot post the table due to confidential information):

I want to obtain the Date ID thats coming from the Dates table. Each row in the Dates table is a week of our calendar year, hence creating a foreign ID that I want to be able to reference between those start and end dates (start_date & end_date). 

[AndreLuiz]

The error " Table of multiple values was supplied when a single value was expected" occurred because, according to your table, an Order can span many days which are contained in different weeks, resulting in multiple IDs, but only one ID value can be returned by the calculated column.

There are some ways to solve the problem:

1st: Choose a criteria to decide which ID must hold, e.g. if an order spans IDs 10,11,12, choose the max ID, or min ID, etc.

2nd: Use only one date as criteria. For your description, if the ID must be the one in which the order was completed a calculated column in the Orders table should solve the problem:

CALCULATE (
    VALUES ( Dates[ID] ),
    FILTER (
        'Dates',
        'Orders'[Ordered_date]  >= 'Dates'[Start_date] && 
        'Orders'[Ordered_date] <=  'Dates'[End_date] 
    )
)

Greetings,

André

[nnouchi]

@AndreLuiz I should have clarified, I want to focus on when the order is made (Ordered_Date)  to assign the key. 

I have tried the formula again but once again I'm getting this message: "Table of multiple values was supplied when a single value was expected". Why can't I use the ID as a non unique key idenitifier in one-to-many relationship?

By the way, I appreciate all of the support on this thread.

Nic

[v-shex-msft]

Hi @nnouchi,

>>"Table of multiple values was supplied when a single value was expected". Why can't I use the ID as a non unique key idenitifier in one-to-many relationship?

Actually, one to many relationship means one unique records match with multiple duplicate records.

Values function can return one or list of result, when it return one, you can use it in calculate columns and measures without 'multiple value issue'.

For your formula, it will filter records based on specific date range from table and return filtered list of column values.

VALUES Function (DAX)

Create and manage relationships in Power BI Desktop

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

Does this work?

=IF(HASONEVALUE(Dates[ID]),CALCULATE ( VALUES ( Dates[ID] ), FILTER ( 'Dates', 'Dates'[Start_date] <= 'Orders'[Ordered_date] && 'Dates'[End_date] >= 'Calendar'[Ordered_Date] ) ),BLANK())