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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Cumulative sum with multiple filters

My objective is to calculate the sum of total population for a city based on 2018 year, and three different wage distribution bins

 

 

Under 30k = 
CALCULATE(
    SUM('Wage Distribution'[Total Population]),
    FILTER (
            'Wage Distribution',
        OR(OR (
            'Wage Distribution'[Wage Bin]="< $10K",
            'Wage Distribution'[Wage Bin]="$10-20k"),
            'Wage Distribution'[Wage Bin]="$20-30k"
        )
            && 'Wage Distribution'[ID Year]="2018"
    )
)

 

 

My code doesn't work, and results in the below error. I've verified that Wage Distribution is set to data type text, ID year is set to number, and Total Population is Whole Number. I don't understand why I'm getting an integer to text error 

 

MdxScript(Model) (10, 16) Calculation error in measure 'Wage Distribution'[Under 30k]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

 

Here's a photo of my table in excel:

 

jsauerla_0-1597752708643.png

 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

If ID Year is set to number, you need to remove the quotes from "2018" to be simply 2018

 

When you use the quotes, DAX assumes it to be text, so for numeric fields just simply state the number. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

As pointed out by @AllisonKennedy , Remove quotes from the year "2018" .

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

As pointed out by @AllisonKennedy , Remove quotes from the year "2018" .

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

That was the issue. Thank you! 

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Try one more option.

 

Go to Power Query and set the Data Type as text.

 

As of now it will be depicting 123ABC -> Change that to Text.

 

Regards,

Harsh Nathani

 

AllisonKennedy
Super User
Super User

If ID Year is set to number, you need to remove the quotes from "2018" to be simply 2018

 

When you use the quotes, DAX assumes it to be text, so for numeric fields just simply state the number. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.