Creating new field for multiple values

[hlewisNYC]

Hello,

I am creating a report to monitor which of my organization's agency partners have updated the plans assigned to them. The records in the database source are the plans. Some agencies have multiple plans which can have different plan statuses (see "Fire" and "Police" in the sample table below - each agency is responsible for two plans). The three possible values for [Plan Status] are "Complete", "Extension "Requested", and "No Response".

Plan ID	Plan Name	Agency	Plan Status
1	Continuity Plan	Fire	Complete
2	Snow Plan	Sanitation	Extension Requested
3	Software Plan	Fire	No Response
4	Rainy Day Plan	Police	No Reponse
5	Search & Rescue Plan	Police	Extension Requested

I am attempting to create a new field in Power BI called [Agency Status] that reports on the agency level rather than the plan level. The [Agency Status] field should have the same values as the [Plan Status] field. The logic should follow:

• If a value for [Agency] IS NOT repeated, apply the value for [Plan Status] as the [Agency Status]. (For example, if an agency only has one plan and if the [Plan Status] is Complete, mark the [Agency Status] field as Complete.)
• If an [Agency] has multiple values (i.e. if an agency has multiple plans)
  • AND if any values in the [Plan Status] field are equal to Complete, then [Agency Status] = "Complete"
• If an [Agency] has multiple values
  • AND if any values in the [Plan Status] contain "Extension Requested" AND NOT "Complete", then [Agency Status] = "Extension Requested"
• If an [Agency] has multiple values
  • AND if all the values are "No Response", then [Agency Status] = "No Response"

Based on this logic (which I am happy to clarify, the following [Agency Status] values should be:

Plan ID	Plan Name	Agency	Plan Status	Agency Status
1	Back-up Plan	Fire	Complete	Complete
2	Snow Plan	Sanitation	Extension Requested	Extension Requested
3	Snow Plan	Fire	No Response	Complete
4	Rainy Day Plan	Police	No Reponse	Extension Requested
5	Back-up Plan	Police	Extension Requested	Extension Requested

As always, I am happy to provide additional detail. Thank you to all those souls who venture to help our a DAX newbie such as myself!

[Zubair_Muhammad]

@hlewisNYC

Try this calculated column

Agency Status =
VAR CountPlans =
    CALCULATE (
        COUNT ( Table_Name[Plan Name] ),
        ALLEXCEPT ( Table_Name, Table_Name[Agency] )
    )
VAR mytable =
    FILTER ( Table_Name, Table_Name[Agency] = EARLIER ( Table_Name[Agency] ) )
RETURN
    IF (
        CountPlans = 1,
        Table_Name[Plan Status],
        IF (
            CONTAINS ( mytable, Table_Name[Plan Status], "Complete" ),
            "Complete",
            IF (
                CONTAINS ( mytable, Table_Name[Plan Status], "Extension Requested" ),
                "Extension Requested",
                "No Response"
            )
        )
    )

[hlewisNYC]

Thank you for the reply, @Zubair_Muhammad. When I add in the necessary table/field names, the calculation is able to run. Unfortunately the values for the number of agencies categorized as each Agency Status are not correct - particularly the count of agenciesthat should fall under "Extension Requested" and "No Response". As I am a beginner in the world of DAX, could you please explain the logic associated with those 2 in the caluclation you shared?

[Zubair_Muhammad]

@hlewisNYC

Hi Lewis.......When I use your sample data I get correct results... See the pic below

Could you share a instance where you get the incorrect Agency Status..... I will try to work it out