Huge Lookup Table with Cross Join - will impact performance?

[campelliann]

Hi,

So I basically need some visuals to filter the revenue by Business Unit, Client and Project. We have a table with the Budget and then another with Revenue.

I need a Lookup table to filter both. I do a cross Joint between 50 BUs, 50 Clients and 50 Projects and then create an ID for each combination I will have a table with 4 columns and 125000 rows.

If this is too expensive I ll need to do the filtering inside a measure (with In values, or treat as)..

Thanks

[bcdobbs]

Why do you need to do a cross join? Would make sense to leave them as separate tables (dimensions) related to each fact table.

Can you supply some example data?

[campelliann]

Hi @bcdobbs,

First and foremost thanks for answering. The thing is that I need this information on the same chart/matrix, thus the need for a lookup table (s) to filter both tables at the same time. Also these lookup tables should be some how connected, because each business unit has diferent clients (and each client different projects). So I dont want a client who does not belong to a particular Business unit to show up on the matrix (perhaps I can just filter and hide the clients for which the measure revenue/budget is 0)..

Revenue Table

Business Unit	Client	Project	Revenue
A	1	x	100
C	2	y	500
D	3	z	200
B	4	D	500

Budget Table

Business Unit	Client	Project	Budget
A	1	x	100
C	2	y	500
Z	3	z	200

[bcdobbs · Accepted solution]

Ok so Power BI automatically hides blanks so you could leave them separate.

Alternatively I'd look to create a project dimension related to both the revenue and budget fact tables as follows:

In Power Query:

1) Reference revenue table.

2) Select your three columns (BU, Clients, Projects) and use remove other columns.

3) Remove duplicates.

4) Repeat with the Budget table.

5) Append the two new tables together.

6) Remove duplicates

7) Add an index column and rename it to ProjectId

😎 Go back to your fact tables and do a merge join to look up the Id column you've just created.

9) Remove the original columns.

Same as you were originally planning but without the cross join,

you'll only have existing combinations in your project dimension table.