Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Here is the solution I found:
https://community.powerbi.com/t5/Desktop/WORKDAY-formula-in-Power-BI/td-p/202383
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
Here is the solution I found:
https://community.powerbi.com/t5/Desktop/WORKDAY-formula-in-Power-BI/td-p/202383
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |