Help with complex query

BarryM — Tue, 17 Jan 2023 08:02:12 GMT

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.

Re: Help with complex query

BeaBF — Tue, 17 Jan 2023 08:18:24 GMT

BarryM Hi! Can you attach the pbix file or paste some sample data and the expected result? To better understand the issue.

BBF

Re: Help with complex query

BarryM — Tue, 17 Jan 2023 12:37:06 GMT

I did include some test code in my post, but I have no way to insert the pbix file

topic Help with complex query in DAX Commands and Tips

Help with complex query

Re: Help with complex query

Re: Help with complex query