Calculating Two Values and Comparing with If Condition

[AkshayManke]

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,

[Anonymous]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AkshayManke]

Hi @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

[Anonymous · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AkshayManke]

Hello @Anonymous,

The given formula worked as expected.. 🙂

Thanks a ton for your great help on this. 

Warm Regards,

Akshay