Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
chockyspice
Frequent Visitor

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 MemberCurrent Status Approving PersonCount Funded Project Approval by Approving PersonProject Count by Team Member
A. SarahPending C. Connor2 (Funded and Sarah was the approving person)

1

C. ConnorFunded A. Sarah1 (Funded and Connor was the approving person)1
M. ShaneFunded C. Connor1 (Funded and Shane was the approving person)1
O. AdamFunded A. Sarah01
S. TaylorPending C. Connor01
W. BenFunded M. Shane01
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1708394566303.png

 

 

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

View solution in original post

8 REPLIES 8
v-yangliu-msft
Community Support
Community Support

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
Solution Sage
Solution Sage

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)
 
talespin_0-1708483567812.png

 

 

Hi,

 

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

chockyspice_0-1708547435799.png

Would you please correct me if I did anything incorrectly?

Much appreciated

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)
 
talespin_0-1708570120370.png

 

v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1708394566303.png

 

 

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

It works now, thank you so much!

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

lbendlin
Super User
Super User

Please provide your sample data separately from your expected outcome.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.