Challenge is setting up a datamodel

[Jeyeline]

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?

[Jeyeline]

Hello @danextian , @rit_ty7 

The source is a single datamodel from azure board source. Sharing the sample Data Record here:

[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 USERELATIONSHIP function 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 USERELATIONSHIP is explicitly used in a measure, meaning they cannot directly filter rows on their own.

Attached is a sample pbix.

[danextian]

How are these work items setup in your source? Are  they from different tables? From the same table but present in each record?

[rit_ty7]

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

[Jeyeline]

Hi @danextian , @rit_ty7 

This 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?

[Anonymous · Accepted solution]

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!

[Jeyeline]

Hello @Anonymous @danextian 

Thanks for your extensive support. @Anonymous  I have tried it with the reference you have shared. It works.

For all fields it is working as expected and I am able to get the data required. However for open parameter, if the calendar filter is for a year,it works. Whereas if I selected a particular data, the corresponding values are not derived. I am checking on it.

Thanks for your support!