Forum Discussion
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:
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.
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)
4 Replies
- AllisonKennedy
Community Champion
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.
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)