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
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.
Highlighted
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

 

 

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.

Top Solution Authors
Top Kudoed Authors