cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KBrady87 Helper I
Helper I

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:

 

DateSiteCategorySales
01/01/2018Site 1Food1000
01/01/2018Site 1Drinks1000
01/01/2018Site 2Food1000
01/01/2018Site 2Drinks 
02/01/2018Site 1Food 
02/01/2018Site 1Drinks1000
02/01/2018Site 2Food1000
02/01/2018Site 2Drinks1000
03/01/2018Site 1Food 
03/01/2018Site 1Drinks1000
03/01/2018Site 2Food1000
03/01/2018Site 2Drinks1000

 

 

Hopefully this makes sense, any help greatly appreciated as always!

 

Thanks,

 

Kevin

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
affan Solution Sage
Solution Sage

Re: How to create a complete lookup table

Hi @KBrady87,

 

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.

CROSS JOIN.gif

 

 

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

 

 

 

View solution in original post

Super User I
Super User I

Re: How to create a complete lookup table

@KBrady87 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)

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

5 REPLIES 5
v-piga-msft Resident Rockstar
Resident Rockstar

Re: How to create a complete lookup table

Hi @KBrady87,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
KBrady87 Helper I
Helper I

Re: How to create a complete lookup table

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 

 

DateSiteCategory
01/01/2018Site 1Food
01/01/2018Site 1Drinks
01/01/2018Site 2Food
01/01/2018Site 2Drinks
02/01/2018Site 1Food
02/01/2018Site 1Drinks
02/01/2018Site 2Food
02/01/2018Site 2Drinks
03/01/2018Site 1Food
03/01/2018Site 1Drinks
03/01/2018Site 2Food
03/01/2018Site 2Drinks

 

My Actual Data

DateSiteCategorySales
01/01/2018Site 1Food1000
01/01/2018Site 1Drinks1000
01/01/2018Site 2Food1000
02/01/2018Site 1Drinks1000
02/01/2018Site 2Food1000
02/01/2018Site 2Drinks1000
03/01/2018Site 1Drinks1000
03/01/2018Site 2Food1000
03/01/2018Site 2Drinks1000

 

 

Highlighted
affan Solution Sage
Solution Sage

Re: How to create a complete lookup table

Hi @KBrady87,

 

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.

CROSS JOIN.gif

 

 

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

 

 

 

View solution in original post

Super User I
Super User I

Re: How to create a complete lookup table

@KBrady87 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)

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

KBrady87 Helper I
Helper I

Re: How to create a complete lookup table

Perfect, thank you very much!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors