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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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