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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.