topic Re: DAX query in DAX Commands and Tips

DAX query

Anonymous — Mon, 19 Feb 2024 18:56:51 GMT

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.

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

Re: DAX query

lbendlin — Mon, 19 Feb 2024 21:06:57 GMT

Please provide your sample data separately from your expected outcome.

Re: DAX query

Anonymous — Tue, 20 Feb 2024 02:04:38 GMT

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

Re: DAX query

Anonymous — Tue, 20 Feb 2024 21:02:25 GMT

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

Re: DAX query

talespin — Wed, 21 Feb 2024 02:46:20 GMT

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)

Re: DAX query

Anonymous — Wed, 21 Feb 2024 06:25:09 GMT

Hi Anonymous ,

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.

Re: DAX query

Anonymous — Wed, 21 Feb 2024 20:31:16 GMT

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

Re: DAX query

Anonymous — Thu, 22 Feb 2024 01:33:36 GMT

It works now, thank you so much!

Re: DAX query

talespin — Thu, 22 Feb 2024 02:48:57 GMT

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)