Forum Discussion
Helper IICalculating Two Values and Comparing with If Condition
Hi Everyone,
I have the below table.
| Project | Activity Name | Status |
Project A | Activity A | Complete |
| Project B | Activity A | On Hold |
| Project C | Activity A | Complete |
| Project D | Activity A | Complete |
| Project E | Activity B | Active |
| Project E | Activity C | Complete |
| Project E | Activity D | On Track |
| Project E | Activity E | On Track |
| Project E | Activity F | Active |
As can be seen, there are multiple projects with associated activities.
In the report i have two visuals. One is for Unique Project Name and another is for Activity.
What i want is, if all the activities of a specific project are having status as 'Complete' then the project should show as completed else not.
Can anyone please help on how to achieve this please. I can provide more information if needed.
Thanks in advance.
Regards,
Hi AkshayManke ,
Do you want to calculate the count of Completed Project Name ? I think you can add number type 1 in [Completed Projects 1] measure directly and then create a new measure based on it.
Completed Projects 1 = VAR _STATUS_LIST = CALCULATETABLE ( VALUES ( 'Project'[Status] ), ALLEXCEPT ( 'Project', 'Project'[Project Name] ) ) VAR _COUNT = COUNTAX ( _STATUS_LIST, [Status] ) RETURN IF ( AND ( "Complete" IN _STATUS_LIST, _COUNT = 1 ), 1 )Count of Complete = SUMX ( VALUES ( Project[Project Name] ), [Completed Projects 1] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi AkshayManke ,
I think you can try this code to achieve your goal.
Measure = VAR _STATUS_LIST = CALCULATETABLE(VALUES('Table'[Status]),ALLEXCEPT('Table','Table'[Project])) VAR _COUNT = COUNTAX(_STATUS_LIST,[Status]) RETURN IF(AND("Complete" IN _STATUS_LIST,_COUNT = 1),"Complete")Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AkshayMankeHi Anonymous,
The given solution is working perfectly as expected. Thanks a lot for the same. Just one more help needed. I am getting the output in string whereas i want to show the status in values in a Card visual. So how to convert the measure from string to number? Can you please help for that ?
I have slightly modified the dax as below.
Completed Projects 1 = VAR _STATUS_LIST = CALCULATETABLE ( VALUES ( 'Project'[Status] ), ALLEXCEPT ( 'Project', 'Project'[Project Name] ) ) VAR _COUNT = COUNTAX ( _STATUS_LIST, [Status] ) RETURN IF ( AND ( "Complete" IN _STATUS_LIST, _COUNT = 1 ), "1" )Warm Regards,
Akshay
Hi AkshayManke ,
Do you want to calculate the count of Completed Project Name ? I think you can add number type 1 in [Completed Projects 1] measure directly and then create a new measure based on it.
Completed Projects 1 = VAR _STATUS_LIST = CALCULATETABLE ( VALUES ( 'Project'[Status] ), ALLEXCEPT ( 'Project', 'Project'[Project Name] ) ) VAR _COUNT = COUNTAX ( _STATUS_LIST, [Status] ) RETURN IF ( AND ( "Complete" IN _STATUS_LIST, _COUNT = 1 ), 1 )Count of Complete = SUMX ( VALUES ( Project[Project Name] ), [Completed Projects 1] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.