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
FrancisSaman
Frequent Visitor

How to use property of selected item?

Hi everyone

 

I working on a report about Employee Absences.

Employees are part of a Department (Employee.DepartmentId) and have Absences (Absences.EmployeeId).

Departments are stored in a separate table.

 

The report has a single-select slicer to select an Employee.

For the selected Employee I need to show, among other things, the total number of Absences for his/her Department.

Basically I want to do a COUNT of Absences WHERE Absences.EmployeeId IN (SELECT Id FROM Employees WHERE DepartmentId = Slicer Employee.DepartmentId) and store this as a measure on the Departments table so that it can be shown using a Card.

 

I already added a denormalization column to Absences containing the Employee Department (Absences.EmployeeDepartment), yet the issue remains that I don't know how to use the Slicer Employee.DepartmentId in my selection query while ignoring the FILTER of the Employee itself.

 

I've been struggling with this issue for quite some time now and would very much appreciate any suggestions!

Please let me know if I need to add extra detail...

 

Kr

Francis 

 

Capture.JPG

1 ACCEPTED SOLUTION

Stumpled upon the solution. Turns out I had already tried the correct statement, but as Measure, rather than a Column.

Measures appear to be depended of the filter context while columns aren't (self-learning this while on the job 😰)...

 

For those of you who are curious:

On the Department table I added a column retrieving the total number (ROWCOUNT) of Absences using RELATEDTABLE.

On Employee, in turn, I retrieved the SUMX of the rowcount (basically just adding up one record) on Absences.

Probably not the cleanest, most advanced way, but given my time-constraint it'll do.

 

If anyone has a clean way of solving this I'd be happy to learn!

 

Have a good one!

Kr

Francis 

View solution in original post

6 REPLIES 6
saraMissBI
Resolver I
Resolver I

Hi @FrancisSaman 

In Power BI desktop, adding filters automatically controls the measures and fields you are putting in your visual. You won't need any extra work to do as Power BI does most of the work for you. If the tables in your model have relationships, then you should just drag your "Absences' field, specify that you want the count (if it does not do it automatically), and the employee filter and department filter will do the work. Could you please share snapshot of what you are getting so that I understand the issue (you can use fake much smaller set of data just to have something to work with)

 

Thank you!

Hi @saraMissBI 

 

First off, thanks for taking the time to help out.

If I understand you correctly I should just add a card showing the count of Absences (counted Ids).

However, as you can see in the screenshot below, it just counts all (13) Absences for the selected employee (Rafael), while there are many more employees in the same Department.

I need to show the total number of Absences for all employees in the same Department as Rafael.

It should show me 73, as that's the number of Absence records I have in my data for all Employees of the "OPS" Department.

 

In other words, I need to get rid of the Employee.Id filter context.

Or I should be able to use the Rafael's DepartmentId, to count all Absences for Employees in that same Department.

 

Thanks for your followup!

 

Capture.JPG

Hi @FrancisSaman 

 

Thank you for the details and screenshot. I see now. Then, you will need to create a calculated column and drag it to your visual then the slicer of Power BI will do the remaining job

 

Absences by Dept = SUMX(FILTER(Employee;Employee[DeptID]=EARLIER(Employee[DeptID]));Employee[Absence])
 
Please let me know if you have questions

Hi @saraMissBI 

 

Thanks for following up.

I don't really understand your use of EARLIER in this case.

Additionally I'm also getting a circular reference warning when I try to create a calculated column on Employee using the statement you provided.

Hi @FrancisSaman 

Check out this tutorial to learn about the use of function EARLIER in roder to get running totals.

Use of Earlier 

Thank you

Stumpled upon the solution. Turns out I had already tried the correct statement, but as Measure, rather than a Column.

Measures appear to be depended of the filter context while columns aren't (self-learning this while on the job 😰)...

 

For those of you who are curious:

On the Department table I added a column retrieving the total number (ROWCOUNT) of Absences using RELATEDTABLE.

On Employee, in turn, I retrieved the SUMX of the rowcount (basically just adding up one record) on Absences.

Probably not the cleanest, most advanced way, but given my time-constraint it'll do.

 

If anyone has a clean way of solving this I'd be happy to learn!

 

Have a good one!

Kr

Francis 

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.