date slicer is not working in 2nd table

[Anonymous]

hi i am facing an issue with one my customer requirement.

the Resource Utilization chart range is not changing which i apply the date slicer on top. can you someone please check & help on this.

https://drive.google.com/file/d/1unKpP5h39F_8_5pl-y2g7fezfYRi2irw/view?usp=sharing 

[v-gizhi-msft]

Hi,

You can try to create an extra date slicer table to filter both two tables.

Best Regards,

Giotto

[Anonymous]

Hi,

I have checked your file and in my opinion, as per the data model (especially the relationships), the "Resource Utilisation" chart is working fine.

For example, if you select 08th Apr 2020 on the date slicer which is based on History[Status end] field, it will first filter the History table and pass the following filter to "Issues" table which is based on the "Key" field.

Key

AURA-1866

OS-690

OS-851

OTBPROD-817

OTBPROD-818

OTBPROD-829

PROM-611

PROM-612

PT-1546

This filter will further filter the "Project Key" field on the Issues table with the following values 

Project Key

AURA

OS

OTBPROD

PROM

PT

This filter on the "issues" table will further filter the "Worklog table" using the project key values mentioned in the table above.

Based on this filter on the Project Keys field, the worklog table's Time Entry Date field has values ranging from 2018 May to 2023 March.

The Resource Utilisation chart is using User Names and Time Entry Logged Time (Sum) fields. Based on the "project key" values, the "Resource Utilisation chart" is working correctly with accurate filters. But it is not filtered based on any date. It is filtered based on "Project Keys" because of the BiDirectional filters you have used in your data table. For example, in the history table, 08th March 2020 is filtering few "Keys/Project keys" which will the filter passed on to the other tables. Not any dates.

It is strange that you have activated bidirectional cross-filtering on all the relationships in your data model which no one does unless they are absolutely sure about it because activating bidirectional cross-filtering on all the relationships on a data model is likely to produce unexpected results and you won't even realize it by looking at the figures reported on the visuals. I think you should look at the relationships once again and decide that you really want to activate bidirectional cross-filtering on all relationships or not. I should also consider maintaining a date table/calendar table in your model with relationships to other fact tables and use the "calendar table" on the slicer if you are looking at filtering multiple visuals with the same date range.

[Anonymous]

i am a little confused on the solution, can we speak to discuss this further.

the filter is setup based on project key because it should show only the resources allocated for that project under that resource utlization.

[Anonymous]

Actually, I did not give any solution. In fact, it is already working as per your requirement on the following assumption.

1. The user selects a date range in the filter.
2. Based on the date slicer, the filter takes all the project keys that are in the date range.
3. Based on the project keys, it is showing the resource utilization in the visual

Why do you think the "Resource Utilisation" visual is showing the wrong data? Once you answer this question or show the expected result with the help of one example (eg: what should the "Resource Utilization" visual show if the user selects 08th April 2020 in the date slicer), it will be helpful for others to understand and resolve the issue.

[bheepatel]

Hi @Anonymous 

The date field (Status end) used in the slicer is not the same as the date field (Time Entry Date) used in the Resource Utilization table. For the other two visual (All Projects and Daily Trend) you have used the "Status end" date field so your slicer works as you want it to. But for the Resource Utilization table, it will not work the way you want it to - you will need to have a separate slicer with the "Time Entry Date" fied.

With that being said, your current slicer still affects the Resource Utilization table. When you change the slicer, have a look at the total values in your Resource Utilization table and you will notice they are changing. This is because of the relationships that exist in your model.

Hope that helps!

[Anonymous]

i understand that, is there anything we can do so the resource utlization date range fliters using the existing slicer instead of building a new slicer.

it is a ask came from one of our customer,  kindly suggest how can we achieve this.

[bheepatel]

Just change the date field in the Resource Utilization table from "Time Entry Date" to "Status end".

Since you have relationships between the two tables, it should work as you want it to.

[Anonymous]

when i change the date field the values are not showing correctly. as it shows based on time entry date.

can you please check i have shared my pbix link to you.