Forum Discussion
Formula not working as expected when using not equal combined with OR (||) operator
Hi,
I'm fairly new to power bi. I've been using this tool for about 5 months. I am trying to create a measure to calculate the total amount based on specific values. The values range from 1-20. I would like the measure to sum the total of a column for values 5-20. I think the best way to write this formula is to use "not equal" (<>) to filter out value 1-4 and my formula is found below. However, the measure is not working as expected and is summing the total based on values 1-20 I tried to filter out 1-4 using the not equal (<>). Please note that the column with the values 1-20 is in text and I've used the values formula to covert that column into numbers.
Can someone please take a look at my formula below and advise why it might not be working?
Total = CALCULATE(SUM('Mock 1042 Data'[Box 2 Amount]),FILTER('Mock 1042 Data', VALUE('Mock 1042 Data'[Unique Form ID]) <> 01 || VALUE('Mock 1042 Data'[Unique Form ID]) <> 02 || VALUE('Mock 1042 Data'[Unique Form ID]) <> 03 || VALUE('Mock 1042 Data'[Unique Form ID]) <> 04))
5 Replies
Remove "VALUE" and check
Hi,
I removed the "value" from the formula and got the below error. It's because the field that I using the apply the filter is set as a "text", which is why I'm using "VALUES"
Error Message:
MdxScript(Model) (16, 132) Calculation error in measure 'Mock 1042 Data'[Total]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
Below is my updated formula:
Total = CALCULATE(SUM('Mock 1042 Data'[Box 2 Amount]),FILTER('Mock 1042 Data','Mock 1042 Data'[Unique Form ID] <> 1 ||'Mock 1042 Data'[Unique Form ID] <> 2 ||'Mock 1042 Data'[Unique Form ID] <> 3 ||' Mock 1042 Data'[Unique Form ID] <> 4))
Any additional help to get this formula working would be grealy appreciatedCheck the data type of your column. Make sure to make them the correct data type. In case it is text type uses A<> "1".
