Forum Discussion
Helper IIIChallenge is setting up a datamodel
Dear Team,
We are working with a datamodel integrated with Microsoft Azure Devops and Our Data model consists of workitem fields. Example, Bug, Task, etc. So each work item will have fields such as ID, Title, Created on, Created by, resolved on, resolved by, verified on, verified by, Assigned To.
Now I have to develop a dashboard where I need to select the employee name from a slicer, and it shows four visualisations as represented below based on the name i have selected
1. Created Work Items
2. Resolved Work Items
3. Verified Work Items
4. Closed Work Items
Since all these fields are required unique field to filter(created by,resolved by, verified by, closed by), I am facing challenges in setting up the datamodel and visualisation. If I maintained entire employee name in a separate datamodel, how that field will be aligned with different field names of a single datamodel.
Can anyone help us on this?
Hi Jeyeline
I created a sample file. Below is a screenshot. You can download my sample file to see details. Hope it would be helpful.
The active relationships are on Created Date and Created By columns. The other relationships are inactive. Then use measures to calculate the Count values and use USERELATIONSHIP function to activate the inactive relationships.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
7 Replies
rit_ty7Advocate I
Hello,
I think to solve this issue you have to create a separate Employee table with unique employee IDs and names. Link this table to the work item table using relationships for fields like Created By, Resolved By, Verified By, and Closed By. This will allow the employee slicer to filter work items based on the selected employee across multiple fields. Create measures or calculated columns for each visualization (Created, Resolved, Verified, Closed) to dynamically display the data based on the slicer selection.
Regards,
Ritesh
JeyelineThis is my final visualisation I would like to get.
If I selected the employee name, each of the visualisation should show unique results.
Example- Jeyeline has been selected in name, so the number of work items which is created by should be shown first, next will be the number of work items resolved by and similarly the number of work items closed by Jeyeline. Since the work items may vary, it should display the work item type as well.
On the other hand, if i selected the date filter as today, it should show
no. of work items created by Jeyeline on today(created on)
no. of work items resolved by Jeyeline on today (resolved on)
no. of work items closed by Jeyeline on today (closed on).
I could not make it possible with the relationships. Can anyone help us?Hi Jeyeline
I created a sample file. Below is a screenshot. You can download my sample file to see details. Hope it would be helpful.
The active relationships are on Created Date and Created By columns. The other relationships are inactive. Then use measures to calculate the Count values and use USERELATIONSHIP function to activate the inactive relationships.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
- danextian
Super User
How are these work items setup in your source? Are they from different tables? From the same table but present in each record?
- danextian
You only need a single calendar table. Establish an active relationship for one of the date columns and set the others as inactive. Use the
USERELATIONSHIPfunction in a measure to activate the inactive relationships when needed. In the screenshot below, the relationships shown with broken lines are inactive.Inactive relationships cannot be utilized unless
USERELATIONSHIPis explicitly used in a measure, meaning they cannot directly filter rows on their own.Attached is a sample pbix.
