Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to Solution.
Hi all,
I was able to solve this issue by replacing the OR operator (<>) with the AND operator (&&). This seem to work and I'm getting the correct results from the measures. Thanks for all your help.
Hi all,
I was able to solve this issue by replacing the OR operator (<>) with the AND operator (&&). This seem to work and I'm getting the correct results from the measures. Thanks for all your help.
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 appreciated
Check the data type of your column. Make sure to make them the correct data type. In case it is text type uses A<> "1".
Thanks. I checked the data type and it'showing as "Text". I've updated my code to use quotes around the values that I'm filtering. Unforunately, the formula is still not excluding values 1-4 from the measure.
Below is the 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"))
I've provided a screen shot example of what I'm trying to accomplish. In my example I should see a total of 8 but my measure is showing 12, which is not correct.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |