Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Calculated column with ALLEXCEPT works in one table but gets error in another table

Hello! 


I have a normal calculated column that works in one of my tables, no problem. Then I made a new table with the same data, but an additional table appended and tried to create the same column. This resulted in the error: "A table of multiple values was supplied where a single value was expected."

 

This is the first, working column:

einarrattgard_2-1655731831355.png

This is the second one that gets an error, and I don't know why.

einarrattgard_3-1655731870419.png

The two tables have the same data.

It is the ALLEXCEPT-function that is struggeling.

Thank you so much for your help!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , I think issue is with +values('Year 2022'[Base])

 

use aggregation like sum/min/max etc

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

Could you pls share your sample data, and remember to remove confidential data.

 

 

Best Regards

Lucien

amitchandak
Super User
Super User

@Anonymous , I think issue is with +values('Year 2022'[Base])

 

use aggregation like sum/min/max etc

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak Thanks, that sort of worked! But what happens then is that the base gets added for each time (it is a cumulative sum) when it is only supposed to be added once. Do you have any tips on how to fix that?
The measure now looks like this:

 

Progress = 
VAR WeekInRow = '2022 Yearly'[Week]
VAR Base = '2022 Yearly'[Base]

RETURN
IF(
    '2022 Yearly'[SumOrLatest] = "Latest",
    '2022 Yearly'[Input Value],
    CALCULATE(
        SUM('2022 Yearly'[Input Value]),
        REMOVEFILTERS('2022 Yearly'[Week]),
        '2022 Yearly'[Week] <= WeekInRow,
        ALLEXCEPT( '2022 Yearly' ,'2022 Yearly'[Week],'2022 Yearly'[Key Result] )
    ) + Base
)

 

 I had to make a variable out of the base value because otherwise I could not get the one from the row it was supposed to calculate.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!