Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BarryM
Helper II
Helper II

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.

2 REPLIES 2
BarryM
Helper II
Helper II

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

 

BeaBF
Impactful Individual
Impactful Individual

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

 

BBF

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors