Forum Discussion
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 m...
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"
)
)
)
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?
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