Create calculated column based on another sheet

[shashar11]

Hello -

I have a spreadsheet with multiple sheets. Sheets 1 has some data .
Sheet 2 has a holiday schedule. I need to upload both sheets to the Power BI and create a calculated column called based on sheet 1 and sheet 2(holiday schedule).
Please note there is no relation between sheet 1 and sheet 2 and different columns names are present in both sheets.
Can someone please advise if its doable and how?

[v-alq-msft]

Hi, @shashar11 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[shashar11 · Accepted solution]

Here is the solution I found:

https://community.powerbi.com/t5/Desktop/WORKDAY-formula-in-Power-BI/td-p/202383

[v-alq-msft]

Hi, @shashar11 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

There is no relationship between two tables. You may create two calculated columns as below.

NewColumn1 = LOOKUPVALUE(Table1[ProductName],Table1[ProductID],Table2[PID])

NewColumn2 = 
CALCULATE(
    SUM(Table2[Sales]),
    FILTER(
        ALL(Table2),
        Table2[PID]=EARLIER(Table1[ProductID])
    )
)

Result:

Table1:

Table2:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@shashar11 , as long as these are common column you can able to create a new column in the table from other tables likes

example new column in sales from City table

City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

City Name = countx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])

You can use additional condition and replace maxx with minx , sumx etc

[Christophe]

The contents of your sheet1 and sheet2 will be transformed into two tables in Power BI.

A "calculated column" in Power BI applies to a row within a table. Usually it won't let you work with two tables especially as they are not related.

What you are probably looking for is a measure. The formula for the measure can take content from multiple tables and will of course depend on your specific requirements.