How to create a complete lookup table

[Anonymous]

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

[affan · Accepted solution]

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

[affan · Accepted solution]

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]

Perfect, thank you very much!

[v-piga-msft]

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

[Anonymous]

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

[PattemManohar · Accepted solution]

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