Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 ar...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

talespin's avatar
talespin
Solution Sage
2 years ago

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)
 

 

 
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    talespin
    Solution Sage
    2 years ago

    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)