PowerBI Accelerator Course Bonus Content - Columns to Scalar Value?

Cole Welsh

Hi everyone!

I'm working on the bonus content part of the PowerBI Accelerator Course for the Line & Column Chart, and I'm getting an error in PowerBI that I don't entirely understand.

In Step 56, you're required to copy/paste the following DAX in for a New Column:

Giving Level = // Assign a Giving Level range to each gift header record based on total gift amount (not split)

// Reference the Giving Level spreadsheet for min/max of each range

CALCULATE(VALUES('Giving Level'[Giving Level Description]),FILTER('Giving Level',Gift[Gift Amount]>= 'Giving Level'[Giving Level Min] && Gift <='Giving Level'[Giving Level Max]))

However, when I put this in, I receive the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

I did some Google-ing, and I'm not exactly sure I'm ending up with any good idea for how to fix this - I've seen that a scalar value is supposed to return one single value? Which makes sense, I think, but I don't know how you're supposed to get that from multiple columns then.

Help?

Dan Snyder

@Cole Welsh
I am not entirely sure this will solve your problem, but I see an extra comma between Level and Gift

CALCULATE(VALUES('Giving Level'[Giving Level Description]),FILTER('Giving Level',Gift[Gift Amount]>= 'Giving Level'[Giving Level Min] && Gift <='Giving Level'[Giving Level Max]))

Matt Thacker

Hey @Cole Welsh, in the second condition of your FILTER expression, the reference to the Gift table isn't specifying a column, it's referencing the entire table.

CALCULATE(
VALUES('Giving Level'[Giving Level Description]),
FILTER(
'Giving Level',
Gift[Gift Amount]>= 'Giving Level'[Giving Level Min] && Gift[Gift Amount] <= 'Giving Level'[Giving Level Max]
)
)

Rebecca Sundquist

@Cole Welsh, you'll find the working DAX for this calculated column in your Lab1b_solution.pbix file. Matt solved it. Thank you for pointing out where the workshop documentation requires an update. ✨ You earn bonus stars for working through the bonus workshop content!!

Giving Level = // Assign a Giving Level range to each gift header record based on total gift amount (not split)

// Reference the Giving Level spreadsheet for min/max of each range

CALCULATE(VALUES('Giving Level'[Giving Level Description]),FILTER('Giving Level', Gift[Gift Amount]>= 'Giving Level'[Giving Level Min] && Gift[Gift Amount]<='Giving Level'[Giving Level Max]))

In other words, we are trying to find the ONE value from the Giving Levels de-coder to apply to each gift amount.

Credit where credit is due: This example was borrowed from @Ellen Smith's Power BI Template for use with Exports.