Help with complex query
Hi, Thanks in advance. I'm trying to creat a new Incidents column in a table based on some complex conditions. I have 48 vars that calculate the count of department names occur in an affected Dept table but also filter based on the "event cataegory" in the issues table. so far this seems to all work. The problem is I need to get each of these values into the new incidents column in the correct row based on Dept. Name but I can't figure out how. Here is some same code I have so far; Incidents = // the var below calculates the number incidents that affect ALL depts var All_Tot = CALCULATE(COUNT('Impacted_Departments'[Impacted_Departments]),FILTER('Impacted_Departments','Impacted_Departments'[Impacted_Departments]="All")) //the following var was intended as a place holder to be able to add a Dept. total with the All_Total var Dept_Total = All_Total ' //the var belaow gets the # of impacted depts filtered for this particular dept. from an impacted deptments table as well as filtered //for the event_category "Cyber Incident" from an issues table var PSPC= CALCULATE(COUNT('Impacted_Departments'[Impacted_Departments]),FILTER('Impacted_Departments','Impacted_Departments'[Impacted_Departments]="Public Services and Procurement Canada (PSPC)"),FILTER(Issues,Issues[Event_Category_12005]="Cyber Incident")) //The below is another var calculated like the on above filtered for the same event_category but for a different dept var TC= CALCULATE(COUNT('Impacted_Departments'[Impacted_Departments]),FILTER('Impacted_Departments','Impacted_Departments'[Impacted_Departments]="Transport Canada (TC)"),FILTER(Issues,Issues[Event_Category_12005]="Cyber Incident")) RETURN I can return the correct count for either of the 2 vars above but I need to be able to add the All_Total with the total for each dept the insert that number into the correct row of the Incidents column based on Dept name. Keep in mind that this needs to be done 48 times in total. I've tried several conditional statements like switch and IF statements but it errors out on the syntax as I cannot figure out how to do it correctly. Thanks again.
