Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
affan
Solution Sage
Solution Sage

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.

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

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

image.png

 





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

Proud to be a PBI Community Champion




View solution in original post

5 REPLIES 5
affan
Solution Sage
Solution Sage

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.

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

 

 

 

Anonymous
Not applicable

Perfect, thank you very much!

v-piga-msft
Resident Rockstar
Resident Rockstar

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

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.
Anonymous
Not applicable

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

 

 

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

image.png

 





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

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.