Forum Discussion
How to create a complete lookup table
I am not sure exactly how to word this question so apologies if this has been answered previously...
Essentially I want to build out a list based on dates, sites and categories. From here I want to merge my sales data, that way I will have a full list of dates, sites and categories regardless of wheteher sales occurred.
As it stands I only have a row in my sales table on the dates sales were registered.
As a simple example this is what I would like to see, I want to build the first 3 columns first with the rows in red the result of this table:
| Date | Site | Category | Sales |
| 01/01/2018 | Site 1 | Food | 1000 |
| 01/01/2018 | Site 1 | Drinks | 1000 |
| 01/01/2018 | Site 2 | Food | 1000 |
| 01/01/2018 | Site 2 | Drinks | |
| 02/01/2018 | Site 1 | Food | |
| 02/01/2018 | Site 1 | Drinks | 1000 |
| 02/01/2018 | Site 2 | Food | 1000 |
| 02/01/2018 | Site 2 | Drinks | 1000 |
| 03/01/2018 | Site 1 | Food | |
| 03/01/2018 | Site 1 | Drinks | 1000 |
| 03/01/2018 | Site 2 | Food | 1000 |
| 03/01/2018 | Site 2 | Drinks | 1000 |
Hopefully this makes sense, any help greatly appreciated as always!
Thanks,
Kevin
Hi Anonymous,
As per my understanding from your question. I have created a sample file which you can download from here.
I have understood that you are having two tables first is your dates table and the second is the transactions table. To acheive your result I have created a custom table with cross joined values from both of these tables.
I used the following DAX to created this custom table.
ResultTable = CROSSJOIN(DateTable,SUMMARIZE(Table1,Table1[Category],Table1[Site]))
You can see the below illustration to understand the results and the attached power bi file.
The lookup column I have created is with the following DAX.
SalesAmount = LOOKUPVALUE(Table1[Sales],Table1[Category],ResultTable[Category],Table1[Dates],ResultTable[Dates],Table1[Site],ResultTable[Site])
This looksup where all three values, date, category and site are same.
The date table I have is having dates within Jan 2018.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Anonymous Please try this as a "New Table"
Test156Out = VAR _MinDate = MIN(Test156Data[Date]) VAR _MaxDate = MAX(Test156Data[Date]) VAR _DateGen = CALENDAR(_MinDate,_MaxDate) VAR _Site = VALUES(Test156Data[Site]) VAR _Category = VALUES(Test156Data[Category]) RETURN CROSSJOIN(CROSSJOIN(_DateGen,_Site),_Category)
5 Replies
Hi Anonymous,
As per my understanding from your question. I have created a sample file which you can download from here.
I have understood that you are having two tables first is your dates table and the second is the transactions table. To acheive your result I have created a custom table with cross joined values from both of these tables.
I used the following DAX to created this custom table.
ResultTable = CROSSJOIN(DateTable,SUMMARIZE(Table1,Table1[Category],Table1[Site]))
You can see the below illustration to understand the results and the attached power bi file.
The lookup column I have created is with the following DAX.
SalesAmount = LOOKUPVALUE(Table1[Sales],Table1[Category],ResultTable[Category],Table1[Dates],ResultTable[Dates],Table1[Site],ResultTable[Site])
This looksup where all three values, date, category and site are same.
The date table I have is having dates within Jan 2018.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi Anonymous,
I'm a little confused about your requirement.
Is the data you shared your data sample? If it is, could you share your desired output so that I can understand your scenario better and help further on it?
Best Regards,
Cherry
Hi Cherry, the table I shared is my desired final outcome.
The table I am trying to create is the first three columns. My acutal data is the same table minus the rows in red. So when I merge on data/site/category I also get an entry for days with no sales,
The problem is I have over 1,000 sites and 25+ categories so manually creating the first three columns isn't feasible.
Hopefully that makes a bit more sense, I have been struggling to articulate this question for a while!
I have included the actual and target data below
Thanks,
Kevin
What I want to make
Date Site Category 01/01/2018 Site 1 Food 01/01/2018 Site 1 Drinks 01/01/2018 Site 2 Food 01/01/2018 Site 2 Drinks 02/01/2018 Site 1 Food 02/01/2018 Site 1 Drinks 02/01/2018 Site 2 Food 02/01/2018 Site 2 Drinks 03/01/2018 Site 1 Food 03/01/2018 Site 1 Drinks 03/01/2018 Site 2 Food 03/01/2018 Site 2 Drinks My Actual Data
Date Site Category Sales 01/01/2018 Site 1 Food 1000 01/01/2018 Site 1 Drinks 1000 01/01/2018 Site 2 Food 1000 02/01/2018 Site 1 Drinks 1000 02/01/2018 Site 2 Food 1000 02/01/2018 Site 2 Drinks 1000 03/01/2018 Site 1 Drinks 1000 03/01/2018 Site 2 Food 1000 03/01/2018 Site 2 Drinks 1000 Anonymous Please try this as a "New Table"
Test156Out = VAR _MinDate = MIN(Test156Data[Date]) VAR _MaxDate = MAX(Test156Data[Date]) VAR _DateGen = CALENDAR(_MinDate,_MaxDate) VAR _Site = VALUES(Test156Data[Site]) VAR _Category = VALUES(Test156Data[Category]) RETURN CROSSJOIN(CROSSJOIN(_DateGen,_Site),_Category)
