Forum Discussion
Is order date in range
Hi Guys
I have a table with dim customer delivery schedule in format like this
country | cst | seq | ref | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
ES | 2062 | 004D1 | ES2062004D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 2062 | 005D1 | ES2062005D1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 |
ES | 2062 | 006D1 | ES2062006D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 2062 | 007D1 | ES2062007D1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 2062 | 008D1 | ES2062008D1 | 1 | 1 | 1 | 0 | 1 | 0 | 0 |
ES | 2062 | 009D1 | ES2062009D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 2062 | 011D1 | ES2062011D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 2064 | 001D1 | ES2064001D1 | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
ES | 3482 | 000D1 | ES3482000D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 3482 | 003D1 | ES3482003D1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 3492 | 000D1 | ES3492000D1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 |
ES | 3515 | 001D1 | ES3515001D1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
ES | 3523 | 000D1 | ES3523000D1 | 0 | 1 | 0 | 1 | 1 | 1 | 0 |
ES | 3543 | 000D1 | ES3543000D1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 |
ES | 3578 | 001D1 | ES3578001D1 | 0 | 1 | 1 | 0 | 1 | 0 | 0 |
ES | 4481 | 001D1 | ES4481001D1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
ES | 9426 | 000D1 | ES9426000D1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 |
ES | 3256 | 000Z0 | ES3256000Z0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
where it basically says the cust with reference ES2062004D1 (Country&cst&seq) only accepts delivery on mo-fr,
cust with reference ES2062005D1(Country&cst&seq) only accepts delivery on mo, tue and thu. etc.
Then I have fact table where i have orders with delivery date
Order # | Country | Cst | Seq | ref | del date |
12365 | ES | 2062 | 004D1 | ES2062004D1 | 19-10-20 |
12366 | ES | 2062 | 004D1 | ES2062004D1 | 20-10-20 |
12367 | ES | 2062 | 004D1 | ES2062004D1 | 21-10-20 |
12368 | ES | 2062 | 004D1 | ES2062004D1 | 22-10-20 |
12369 | ES | 2062 | 004D1 | ES2062004D1 | 23-10-20 |
12370 | ES | 2062 | 004D1 | ES2062004D1 | 24-10-20 |
12371 | ES | 2062 | 004D1 | ES2062004D1 | 25-10-20 |
12372 | ES | 2062 | 004D1 | ES2062004D1 | 26-10-20 |
12373 | ES | 2062 | 004D1 | ES2062004D1 | 27-10-20 |
12374 | ES | 2062 | 004D1 | ES2062004D1 | 28-10-20 |
12375 | ES | 2062 | 004D1 | ES2062004D1 | 29-10-20 |
12376 | ES | 2062 | 004D1 | ES2062004D1 | 30-10-20 |
12377 | ES | 2062 | 004D1 | ES2062004D1 | 31-10-20 |
12378 | ES | 2062 | 004D1 | ES2062004D1 | 01-11-20 |
12379 | ES | 2062 | 004D1 | ES2062004D1 | 02-11-20 |
12380 | ES | 2062 | 004D1 | ES2062004D1 | 03-11-20 |
12381 | ES | 2062 | 004D1 | ES2062004D1 | 04-11-20 |
12382 | ES | 2062 | 004D1 | ES2062004D1 | 05-11-20 |
what i want to achieve is I want to know if the del date is a day when the customer accepts delivery.
What is the best solution
I am thinking to create a calculated column that would calculate something like
if dim customer delivery schedule ref = fact ref and the delivery date is on days 1-5 then 1 else 0
- how should i approach this should I add this table to the model and create relationship
- should i not connect it to the model and use is as lookup table - this can be eventually done also in the query editor (i would assume)
- or somehow create calculated column using dax
I would be very thankful if someone have experience with similar scenario and can recommend a solution or at least suggest direction
thank you very much
miba
Hi Anonymous ,
I would transform (unpivoting the day columns) the dates on dim customer delivery schedule to rows, having only two columns (day and delivery)..
so you can calculate if your customer has 1 on those dates comparing with another table....
See the attached solution using Power Query.
Here I've created a column on delivery table to check if has the date or not.
4 Replies
Hi Anonymous ,
First go to power query>select Columns Mon-Sun >unpivot the columns;
Then back to model view>create a relationship between the fact tabel and the unpivot table;
Then create a calculated column as below:
Column = var _search=LOOKUPVALUE('Unpivot table'[Value],'Unpivot table'[ref],'Table (2)'[ref],0) var _search2=LOOKUPVALUE('Unpivot table'[Value],'Unpivot table'[Attribute],'Table (2)'[Weekday],0) Return IF(_search=1&&_search2=1,TRUE(),FALSE())Or a measure as below:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi Anonymous ,
I would transform (unpivoting the day columns) the dates on dim customer delivery schedule to rows, having only two columns (day and delivery)..
so you can calculate if your customer has 1 on those dates comparing with another table....
See the attached solution using Power Query.
Here I've created a column on delivery table to check if has the date or not.
Hi Anonymous,
You can try the following,
-> create a new column in your customer dimension like:
Accepts Delivery = if([Monday] = 1, "2|", "|" ) & if([Tuesday] = 1, "3|", "|" ) & if([Wednesday] = 1, "4|", "|" ) & if([Thursday] = 1, "5|", "|" ) & if([Friday] = 1, "6|", "|" ) & if([Saturday] = 1, "7|", "|" )& if([Sunday] = 1, "1|", "|" )-> create a day of week column in your fact table (type == text)
Day of Week = WEEKDAY([del date])-> create a calc in your fact table to identify if the day of week exists in the Accepts Delivery String
Good Date = if(FIND([Day of Week], RELATED('dim customer delivery schedule'[Accepts Delivery]), 1, 0)>0, TRUE(), FALSE())Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Hi,
I would like to help. The second table, when pasted in MS Excel, does not appear in a proper format. The first one appears just fine. Please paste both tables in an MS Excel workbook and share the download link of that workbook.
