DAX query

[chockyspice]

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

[v-yangliu-msft]

Hi  @chockyspice ,

If it's convenient, you can show the error message that doesn't work as a picture and we can help you better!

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.

[talespin]

hi @chockyspice 

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)

[chockyspice]

Hi,

   Thank you for your helps. But I still have an issue to obtain the same results from you query.

Would you please correct me if I did anything incorrectly?

Much appreciated

[talespin]

hi @chockyspice 

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)

[v-yangliu-msft · Accepted solution]

Hi  @chockyspice ,

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

[chockyspice]

It works now, thank you so much!

[chockyspice]

Thank you very much, but the query is not working after the modification. 

[lbendlin]

Please provide your sample data separately from your expected outcome.