cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KBrady87 Regular Visitor
Regular Visitor

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 Established Member
Established Member

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
Community Support Team
Community Support Team

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 Regular Visitor
Regular Visitor

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 Established Member
Established Member

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 Regular Visitor
Regular Visitor

Re: How to create a complete lookup table

Perfect, thank you very much!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors