Forum Discussion
DAX query
Hi,
I am new in learning DAX query. I am trying to count the project numbers based on approving person, but I would like the results to be in same row as each Team Member since some of them are the team member and approving person. Would you recommend me any DAX query I could use?
I have tried this query, but it is not calculated correctly.
Count of Funded Project Approval by Approving Person =SUM(
IIF(
Field(Count Funded Project Approval by Approving Person.Value) AND Fields!Current_Status.Value = "FUNDED",
1,
0
)
)
Any assistance is greatly appreciated.
PT
| Team Member | Current Status | Approving Person | Count Funded Project Approval by Approving Person | Project Count by Team Member | |
| A. Sarah | Pending | C. Connor | 2 (Funded and Sarah was the approving person) | 1 | |
| C. Connor | Funded | A. Sarah | 1 (Funded and Connor was the approving person) | 1 | |
| M. Shane | Funded | C. Connor | 1 (Funded and Shane was the approving person) | 1 | |
| O. Adam | Funded | A. Sarah | 0 | 1 | |
| S. Taylor | Pending | C. Connor | 0 | 1 | |
| W. Ben | Funded | M. Shane | 0 | 1 |
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Count Funded Project Approval by Approving Person = var _table= SUMMARIZE( 'Table','Table'[Team Member]) var _table2= ADDCOLUMNS( _table,"1", COUNTX( FILTER('Table', 'Table'[Approving Person]=EARLIER([Team Member])&&[Current Status]="Funded"),[Current Status])) var _value= MAXX( FILTER(_table2,[Team Member]=EARLIER('Table'[Team Member])),[1]) return IF( _value=BLANK(),0,_value)Project Count by Team Member= CALCULATE( DISTINCTCOUNT('Table'[Team Member]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
8 Replies
Please provide your sample data separately from your expected outcome.
Hi Anonymous ,
Here are the steps you can follow：
1. Create calculated column.
Count Funded Project Approval by Approving Person = var _table= SUMMARIZE( 'Table','Table'[Team Member]) var _table2= ADDCOLUMNS( _table,"1", COUNTX( FILTER('Table', 'Table'[Approving Person]=EARLIER([Team Member])&&[Current Status]="Funded"),[Current Status])) var _value= MAXX( FILTER(_table2,[Team Member]=EARLIER('Table'[Team Member])),[1]) return IF( _value=BLANK(),0,_value)Project Count by Team Member= CALCULATE( DISTINCTCOUNT('Table'[Team Member]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
hi Anonymous
This is a measure
Count of Funded Project Approval by Approving Person =VAR _TeamMember = SELECTEDVALUE(Project[TeamMember])VAR _Count = CALCULATE( COUNT(Project[ApprovingPerson]), REMOVEFILTERS(Project), Project[ApprovingPerson] = _TeamMember && Project[CurrentStatus] = "Funded")RETURN COALESCE(_Count,0)hi Anonymous
What I shared earlier was a measure, you can not directly use a measure as calculated column.
If you need Calculated column, use this
Count of Funded Project by Approving Person =VAR _TeamMember = Project[TeamMember]VAR _Count = CALCULATE( COUNT(Project[ApprovingPerson]), REMOVEFILTERS(Project), Project[ApprovingPerson] = _TeamMember && Project[CurrentStatus] = "Funded")RETURN COALESCE(_Count,0)
