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.
As the title states i need to sum the "Amount" column but only if there is a date in the "Date" column.
I am trying to create a new DAX measure but have hit a roadblock.
I currently have:
=SUM(Table[Amount Column])
I am still a complete novice with DAX and am starting to get frustrated as this seems like such a simple task.
I look forward to any help you are willing to give in the hopes of finding a solution to my problem.
Solved! Go to Solution.
Hi @PaulMac,
The issue is in the middle of your measure the measure should look like this
CALCULATE ( SUM ( All_Complaints[Amount of Compensation Paid (£)] ), FILTER ( ALL ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid] <> BLANK () ) )
Previous measure marked in bold the incorrect bracket:
=CALCULATE ( SUM( All_Complaints[Amount of Compensation Paid (£)] ), FILTER ( All ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid] ) <-this is the incorrect bracket <> BLANK () ) )
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @PaulMac,
When you are refering to make the sum of the Amount, what do you mean exactly, you want to return the value of the Amount if there is no date, or do you want to sum the whole column values when the date is not null.
Based on the dax formula you have you should use a calculated measure:
Measure = CALCULATE ( SUM ( Table1[Amount] ); FILTER ( ALL ( Table1[Date] ); Table1[Date] <> BLANK () ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSorry @MFelix but i get this error
This formula is invalid or incomplete: 'The expression is not valid or appears to be incomplete. Please review and correct the expression.
The syntax for ')' is incorrect. (DAX(
=CALCULATE (
SUM( All_Complaints[Amount of Compensation Paid (£)] ),
FILTER ( All ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid]) <> BLANK () )
)
Any thoughts on how I can proceed?
Hi @PaulMac,
Remove the last ) from your formula, you have to many brackets.
While copy pasting something went wrong and you have the addtional one
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I removed the last bracket as per your advice but now I get the following error:
This formula is invalid or incomplete: 'Calculation error in measure 'All_Complaints'[Compensation Paid]: A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'.
Any other ideas?
Hi @PaulMac,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @PaulMac,
The issue is in the middle of your measure the measure should look like this
CALCULATE ( SUM ( All_Complaints[Amount of Compensation Paid (£)] ), FILTER ( ALL ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid] <> BLANK () ) )
Previous measure marked in bold the incorrect bracket:
=CALCULATE ( SUM( All_Complaints[Amount of Compensation Paid (£)] ), FILTER ( All ( All_Complaints[Date Compensation Was Paid] ), All_Complaints[Date Compensation was Paid] ) <-this is the incorrect bracket <> BLANK () ) )
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for the reply @MFelix but I get the error message
The syntax for ';' is incorrect.
I subbed the ; for , but still get an error
I am doing this on an Excel Pivot Table and not I Power BI if that makes any difference.
Hi @PaulMac,
That as to do with regional settings, replace the ";" by "," on the measure.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHmmmm, I did substitue the ; for , but still get an error unfortunately
As I mentioned, I doing this measure on a pivot table in Excel instead of Power BI if that makes any difference.
The formula is the same for both instances
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thanks for your reply.
Yes, I want to sum the whole column values when the date is not null.
I have amended my first post as I am working with DAX and not M as I originally stated, see original post for the measure I have so far
Thanks
Paul
Just adjust my response based on your adjustment of the first post.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |