Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to add two columns / sheet from a different sheet for the dashboard, but running into a 'Blank' statement if the corresponding row doesnt exist in one of the columns / sheet.
E.g.
Sheet 1
Fruit | Price |
Apple | $30 |
Orange | $45 |
Bannana | $25 |
Pear | $15 |
Water Melon | $30 |
Sheet 2:
Fruit | Price |
Apple | $20 |
Bannana | $10 |
Pear | $40 |
Watermelon | $25 |
Orange doesnt exist in sheet 2
I am trying to use the following formula and it gives me 'Blank' on dashboard when the filter on the dashboard is set to orange
Dashboard: Filter is set to Orange
Total Price = sum('Sheet1'[Price], 'Sheet2'[Price])
Solved! Go to Solution.
and
trying to get sum up these two values and I am getting an error.
The formula : =sum('sheet1'[Amount], 'sheet2'[payment]) works only if both these sheets have the value determined by the filter on the dashboard
Try
My Sum = SUM('Sheet1'[Amount]) + SUM('Sheet2'[payment])
works just fine. thank you .. It was so simple, I was over complicating it ...
Doesnt work 😞
you'll need to be more specific, what result or error are you getting? can you share a sample PBIX ?
Create a dimension table like
Fruits =
DISTINCT (
UNION ( ALLNOBLANKROW ( 'Sheet1'[Fruit] ), ALLNOBLANKROW ( 'Sheet2'[Fruit] ) )
)
and link that to both tables in a one-to-many relationship with a single direction. Use the column from the new table on any visuals or filters and it should be fine.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |