Forum Discussion
Matrix calculation Issue - DAX
krishnakanth240 ,
Hi ,
This is giving correct billable utilization values, but the problem is that other resources sponsors are also showing(The rows were Time_in_Hr and Billable_Utl columns are blank) since we removed the filters on the requestors..
Also the Non Billable_Utl should be blank for all requestors except Requestor = "Non- Billable" (it is showing 18.04% for all requestors..)
if i apply Time_In_Hr = is not blank on the filter pane of this matrix visual it will work.. But this Leave_Hour measure is causing issue in my another table viusual.. where all requestors are showing irrespective of the resource name..
So i created a duplicate of the table( vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User) where i filtered only the non billable rows(Billable column = Non-Billable) .. and updated the measures accordingly.. and it is working fine..Thank you so much for all the suggestions 🙂 I really appreciate it.
Hi WinterGarden
Can you provide the sample data of columns with the tables to work on the above DAX measures to check. Thank you!
Hi krishnakanth240 ,
This is the sample data, please note that i have two filters FiscalWeek and resource name..FiscalWeek Resource Requestor Billable Leave_Type Time_In_Hr 20 PB A Billable 9.3 21 PB A Billable 10.83 22 PB A Billable 6.75 23 PB A Billable 9.87 20 PB B Billable 6.23 21 PB B Billable 4.17 22 PB B Billable 4.7 23 PB B Billable 9.33 20 PB C Billable 8.28 21 PB C Billable 5.98 22 PB C Billable 5.62 23 PB C Billable 3.87 20 PB D Billable 1.62 21 PB D Billable 8.62 22 PB D Billable 3.31 23 PB D Billable 3 20 PB Non-Billable Non-Billable NULL 0 21 PB Non-Billable Non-Billable NULL 0 22 PB Non-Billable Non-Billable LeaveType 16 23 PB Non-Billable Non-Billable NULL 0
Standard_Hr1 is from a different table(vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data), that has many to many relationship with the table: vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User
Below is the sample data for standard hour:FiscalWeek Resource stdd_hr 20 PB 40 21 PB 40 22 PB 40 23 PB 40 Hi WinterGarden
Thanks for sharing for one table.
Could you please share these inputs.
a)Some more data of records for these columns(Fiscal Week, Resource, stdd_hr) for 'Table' - vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data
b)Sample data of rows for these columns(SponsorName, SponsorArea, SponsorRegion, SponsorCountry) with records for the above 'Table' you shared - vw_Timely_Ehub_EA_Work_Flow_Tracker_Data_User
Hi krishnakanth240 ,
a) vw_EHub_Fiscal_Calendar_Headcount_Tracker_Data : stdd_hr is same for all the resource.. 40 hrs per week
b) (SponsorName, SponsorArea, SponsorRegion, SponsorCountry):
AJT, APAC, OCEANIA, Australia - for all rows