Forum Discussion

hlewisNYC's avatar
hlewisNYC
Frequent Visitor
8 years ago

Creating new field for multiple values

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 IDPlan NameAgencyPlan Status
1Continuity PlanFireComplete
2Snow PlanSanitationExtension Requested
3Software PlanFireNo Response
4Rainy Day PlanPoliceNo Reponse
5Search & Rescue PlanPoliceExtension 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 IDPlan NameAgencyPlan StatusAgency Status
1Back-up PlanFireCompleteComplete
2Snow PlanSanitationExtension RequestedExtension Requested
3Snow PlanFireNo ResponseComplete
4Rainy Day PlanPoliceNo ReponseExtension Requested
5Back-up PlanPoliceExtension RequestedExtension 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!

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      hlewisNYC
      Frequent Visitor
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        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