cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chienim
Frequent Visitor

Create relarionship between a DATE and a RANGE OF DATES?

Hello,

 

I just started PowerBI for a few days,

 

currently I have some problem merging 2 tables in terms of date,

 

the data sample looks like below:

(1)Table A: Order fact which contains [OrderDate],[OrderNo]and other details

 

OrderDate OrderNo Qty ItemID ...

2016/03/03   001      1      123

2016/05/01   002      3      456

 

 

(2)Table B: A table of Sales campaign data which contains [Camp_ID][StartDate],[EndDate],[ItemID] and others

 

Camp_ID StartDate EndDate ItemID ...

CP001      2016/03/01  2016/04/01   123  ...

CP002      2016/03/15  2016/06/01   456  ...

 

 

What I'm trying to do is to create a relationship between these 2 tables that I can use [Camp_ID] as a filter for analysis.

 

I tried to create a unique key in each table to link them but it looks very unrealistic and non-expandable;

(e.x. CAMP_key = IF(DATEVALUE("06/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("07/31/2014") ,"CAMP1",
IF(DATEVALUE("08/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("08/31/2014") ,"CAMP2",
IF(DATEVALUE("10/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("10/31/2014")&&[ItemCD]="8287512" ,"CAMP3",

...........

 

 

Please let me know if you need any info as I think I didn't explain it very clearly.

ANY ideas and advice to help with this problem will be Greatly appeciated. Thank you very much!

 

 

1 ACCEPTED SOLUTION

I would add another fact-table that allocates the sales to each campaign-day. That's a bit like a budget-table holding planned sales for multiple planning events (multiple campaigns) for the same dates and items. This allows for relative simple measures then:

 

AvgCampSales = AVERAGE('CampaignSales'[CampaignSales])

The table can be created like this:

 

CampaignSales = GENERATE(Camapaign, FILTER(ALLNOBLANKROW(DimDate[Date]), DimDate[Date]>=EARLIER(Camapaign[CampaignStartDate]) && DimDate[Date]<=EARLIER(Camapaign[CampaignEndDate])))

& you need an additional column to it:

 

CampaignSales = 'Order'[AvgSalesAmt]

 

You need to connect it like this:

PBI_CampaignManagement.png

 

Have a look a the file:

https://www.dropbox.com/s/sx28mk4c00ip740/PBI_CampaignManagement5.pbix?dl=0

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
chienim
Frequent Visitor

Hi guys,

 

Thanks for all the comments! I have read them all and let me add some more info about my requirement if it helps.

 

@ImkeF,about the question if there are multiple campaings for one Item ID, Yes there are.

The table is like this:

campaign_multipleItem.jpg

 

It can be multiple Campaign for one Item and it can be multiple items in the same campaign.

 

@bourquejeff,

About the calender solution, yes I have created a calender to try to link the two tables.

The relationship now looks like this:

relationship.jpg

(Sorry if there are some JP characters just ignore them 🙂

 

The next problem is that there are 2 date column in M_Campaign (Start Date and End Date) which both are not Equal to OrderDate in Order table, so I am struggled here about how to build relation of them.

 

@Baskar,

The issue I got here is there is actually no Camp_ID in Order table, which I think I may be able to handle it in SQL before taking it into PowerBI but stucked here when using DAX,

Though as you can see the screenshot of M_Campaign, those are the only infos contained in the table.

Another way to say my requirement is to join the Camp_ID into Order tables then I should be OK to do everything I need after then. 🙂

 

Again thanks for all the comments!

I would add another fact-table that allocates the sales to each campaign-day. That's a bit like a budget-table holding planned sales for multiple planning events (multiple campaigns) for the same dates and items. This allows for relative simple measures then:

 

AvgCampSales = AVERAGE('CampaignSales'[CampaignSales])

The table can be created like this:

 

CampaignSales = GENERATE(Camapaign, FILTER(ALLNOBLANKROW(DimDate[Date]), DimDate[Date]>=EARLIER(Camapaign[CampaignStartDate]) && DimDate[Date]<=EARLIER(Camapaign[CampaignEndDate])))

& you need an additional column to it:

 

CampaignSales = 'Order'[AvgSalesAmt]

 

You need to connect it like this:

PBI_CampaignManagement.png

 

Have a look a the file:

https://www.dropbox.com/s/sx28mk4c00ip740/PBI_CampaignManagement5.pbix?dl=0

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

chienim
Frequent Visitor

Hi,

 

Thanks for the clear sample!

I quicklt tried it with a little bit change (There are multiple camp_ID so couldn't do N:N relationship between Campaign and CampaignSales, I added a key column as [Date]&[ItemID] instead)

 

but currently the relationship seems works well and I think I'm able to play with the data. Thank you very much!

Baskar
Resident Rockstar
Resident Rockstar

Don't worry dude i will help u .

 

In both Table u have Date and Camp_ID right ? if yes do one thing 

 

create calculated column on both table like 

 

Column = Format( " Your Date " , "YYYYMMDD") & "-" & Camp_ID

 

this will work if u have unique column in Date and Camp_ID at anyone table end.

 

 

let me know wore help on DAX. I wil help u 

 

ImkeF
Super User II
Super User II

Have a look if you find some useful information in here:

 

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

http://www.wiseowl.co.uk/blog/s2472/multiple-relationships.htm

http://www.thebiccountant.com/2016/12/01/analyzing-events-with-a-duration-in-dax-further-simplificat...

 

As you will see, is that the right way to do this depends on what you actually want to analyze further. So for a recommendation we would need some details about your specific demand.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

chienim
Frequent Visitor

Hi!

 

Great thanks for the info first, yes I'll explain more detail about what I'm trying to do;

 

So you can see here as example, I have 2 tables 'DM_F_Order' and 'M_Campaign',

Order_Model.jpgCampaign_Model.jpg

 

What I would like to do is the analysis of this:

 

Calculate the sum of Sales qty(or sum of sales amount) of each itemCD (Sales qty info is stored in 'DM_F_Orders'), which is to be filtered by  Campaign_ID, average sales amount by day during campaign days, compared with yearly average of each Item.

 

**Edit: The relationship(physically) between the 2 tables is, when Order and Campaign table have common ItemCD and OrderDate is located between Camp_ST_DATE and Camp_ED_DATE, then the order row is considered as the (CP00X)campaign target.        i.e. "IF (Order.ItemID=Campaign.ItemID AND Campaign.ST_DATE<=Order.OrderDate<=Campaign.ED_DATE), Campaign.Camp_ID"...

 

 My idea was to build a link table between these 2 tables though the biggest struggle is I couldn't think of a key to build up relation for them.

 

Thanks for any advise!

chienim
Frequent Visitor

Just want to add some more comment about this problem (as I haven't solve this out yet),

 

I tried to first use ALLSELECTED function to link Order table with Campaign table, set up a Campaign Key to build the relationship.  The filter I thought in Order looks like this:

CAMPKey = IF([OrderDate]<=ALLSELECTED(M_Campaign[Camp_ED_DATE])&&ALLSELECTED(M_Campaign[Camp_ST_DATE])<=[OrderDate]&&[ItemCD]="1234",'M_Campaign'[Camp_CD],"NotCampaign")

 

(M_Campaign is the Campaign table which stores Campaign start date, end date)

 

 

Which I thought if I can describe "OrderDate is located between ST_DATE and _ED_DATE" WHERE Item Code = the Item Code in table Order,

 

though as imagined it didn:t work well, since I'm not sure how should I describe "WHERE" for the formula in my DAX.

 

Thanks again for ANY help!

@ImkeF is right - you cannot create a relationship with a DAX expression. You must create a dimension table in your data model that you can then use to create relationships with the two "fact" or "data" tables you mention above. 

 

It seems that the best solution in your case would be to create a "Date" or "Calendar" table. If you plan to add new order dates and campaign dates to your data, you'll want it to be a dynamic Date table that continues to incorporate new dates as time moves forward.

 

This post describes in detail how you would create one using "Power Query" in Excel. Fortunately, Power Query is hiding inside Power BI, so you can follow the exact same steps in Power BI, but you'll want to know the following:

 

1. Instead of starting from the "Other Sources" button in Excel, you'll use the "Get Data" button in Power BI. From the dropdown, you'll see the same "Blank Query" option he references.

 

2. Instead of clicking the "Invoke" button, simply hit "enter."

 

3. Find the earliest date that you care about in your data and enter it as the start date. Follow his steps to do the rest.

 

4. Once you've created all the columns you want in your Date table, rename the query in the "Query Settings" pane on the right to "Calendar" or "Dates."

 

5. Click Home>Close & Apply

 

5. Then go to the "Relationships" view on the top left of your screen (third choice down), and you'll see your new table along with the others you've already loaded to your data model.

 

6. Click and drag from the "Date" field in your "Calendar" table to the fields in your "data" tables that contain dates. Make sure the data types of the date columns you're creating the relationship with are also set to the "Date" data type. This change can be made by clicking the "Edit Queries" button on the Home menu, choosing the query that creates your data table, selecting the column with dates, and choosing "Date" from the "Data Type" dropdown in the "Transform" group.

 

7. Once these relationships are created (from the  "Calendar[Date]" column to one data table, and from the "Calendar[Date] column to the other data table), you can then go to the "Report" view (top left of your screen, top choice), right click on one of your data tables, and choose "New Measure" to create measures that allow you to return what you are wanting to return using data from both tables.

 

I hope this helps. Let me know if you need more detail or if you run into issues.

 

 

Sorry for taking so long to reply, too busy at the moment.

Struggling a bit to get your requirement, but one thing I can say is that you cannot "write" relationships between your tables.

Your tables need to be connected via their columns. The only column I can spot in your model is ItemID - is that correct?

Are there multiple campaigns per ItemID?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors