Question about joins and variables

[Anonymous]

Hi All

I am new to Power BI and have a couple of questions:

Q1:

I have imported to straight tables as below and want to fully join them as part of my data modeling:

Table 1 = Emp Table and is as follows

Emp_No, Emp_Name

1001, Fred Bloggs

1002, John Doe

Table 2 = Date Table

Date

01/01/20

08/01/20

15/01/20

I want table 3 as follows:

Emp_No, Date

1001, 01/01/20

1001, 08/01/20

1001, 15/01/20

1002, 01/01/20

1002, 08/01/20

1002, 15/01/20

Any ideas or instructions on how to create?

Question 2

Is there a concept to hold variables at the report level that can be used at all different queries?

For example set to and from dates in total and then use these dates in each separate data pull?

If so how?

Thanks in anticipation

[Anonymous]

Apart from the t1 alias being in the wrong place, nobody else mentioned using square brackets around the table variable, instead of an alias. Changing the update statement to the following will work too:

UPDATE t1
SET
t1.SportName = [@t]._SportName
FROM
@t INNER JOIN tblSport t1 ON t1.Lang = [@t]._Lang

[Anonymous]

Hi

Again I am new to this where do you post this code.

I am afraid you are going to have to take this right back to basics for us newbies

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can use the function CROSSJOIN to create the table 3 in Power BI Desktop:

1. Navigate Modeling ribbon, click "New table“

2. Input the following formula in formula bar

Table = CROSSJOIN(SELECTCOLUMNS('Date',"Date",'Date'[Date]),SELECTCOLUMNS('Emp',"Emp_No",'Emp'[Emp_No]))

3. Create Table visual: drag the field Date and Emp_no onto the visual

https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax

Best Regards

Rena

[amitchandak]

You can use crossjoin, it will return table

table3 = crossjoin(table1,table2)

Use can use var for variable

measure

var _max = 1 //or any calculation

You can use summarize , calculatetable as var etc for subquery implementation

[Anonymous]

Hi

This looks the sort of functionality and clever trick I am looking for but as I said I am new to this

And at the risk of looking dumb where do I enter the dax code?

Do I have to pick a data source?

Sorry almost there 🙂