topic Re: Dax Formula with 1:M Relationship in DAX Commands and Tips

Dax Formula with 1:M Relationship

Anonymous — Sat, 06 Mar 2021 22:21:44 GMT

Hi Everyone,

How can I find the %schedule out of capacity for each month?

I have two tables.

Table1 contains Unique Roles and respective Capacities in each month.

TB1:

ROLE	FEB-CAPACITY	MAR-CAPACITY	APR-CAPACITY	MAY-CAPACITY
A	150	172.5	157.5	165
C	172.5	150	165	172.5
E	157.5	165	172.5	150
F	165	157.5	150	157.5

Table2 contains Roles, EMP_ID, Project Label (Billable,Holiday..etc) and their respective Schedule in each month.

TB2:

ROLE	EMP_ID	Project Label	FEB-SCHEDULE	MAR-SCHEDULE	APR-SCHEDULE	MAY-SCHEDULE
A	1	Billable	135	172.5	157.5	150
F	3	Billable	0	86.25	150	165
C	8	Billable	30	37.5	0	45
A	14	Holiday	45	52.5	7.5	0
E	26	Billable	80	125	64	78
F	47	Holiday	65	105	97	0
C	87	Billable	35	37.5	48	98
F	92	Billable	59	0	65	110
E	98	Billable	25	45	70	85

Based on the Roles in the both tables I created One to Many relationship.

I can write Dax to findout %schedule out of capacity for each month when I select Single role by using Slicer.

%Schedule FEB= DIVIDE(SUM(TB2[FEB-SCHEDULE]), SUM(TB1[FEB-CAPACITY])*COUNT(TB2[ROLE]), 0).

This formula is not working when I select multiple roles/all the Roles.

-How can I write the dax formula to findout %schedule out of capacity for each month for all roles?

I got the answer by using Merge Operation & Lookupvalue. But, Is there any way to write DAX formula to findout the answer while using One to Many Relationship?

Thanks in Advance.

Re: Dax Formula with 1:M Relationship

CNENFRNL — Mon, 08 Mar 2021 00:25:47 GMT

Anonymous , simply unpivotting your dataset might render the scanrio way much easier,

Re: Dax Formula with 1:M Relationship

Anonymous — Sun, 07 Mar 2021 12:53:22 GMT

Hi CNENFRNL,

Thanks for your reply. But the solution was wrong,

If you see For role A in month May,

Total Schedule: 150+0= 150

Total Capacity: 165+165=330(Because we have two EMP_ID with same Role A in month May)

%Schedule of A in May :150/330= 45.45%(Actual Answer).

Hope you understand my point.

Thank You.

Re: Dax Formula with 1:M Relationship

CNENFRNL — Sun, 07 Mar 2021 19:08:29 GMT

Anonymous , so you might want to tweak the measure this way

% schedule out of capacity = DIVIDE ( CALCULATE ( SUM ( SCHEDULE[SCHEDULE] ) ), CALCULATE ( SUM ( CAPACITY[CAPACITY] ) ) * DISTINCTCOUNT( SCHEDULE[EMP_ID] ) )

Re: Dax Formula with 1:M Relationship

Anonymous — Sun, 07 Mar 2021 19:36:51 GMT

CNENFRNL , That's what exactly I did and also mentioned it in my question.

%Schedule FEB= DIVIDE(SUM(TB2[FEB-SCHEDULE]), SUM(TB1[FEB-CAPACITY])*COUNT(TB2[ROLE]), 0).

But this formula is not working for %schedule out of capacity for each month for all roles.

% schedule out of capacity for May for all Roles=Total Schedule in May/Total Capacity in May

=731/1447.5

= 50.50% ( Actual Answer)

You can see, in your formula % schedule for May for all Roles = 731/(645*9)

=12.59%( wrong Answer)

Re: Dax Formula with 1:M Relationship

CNENFRNL — Mon, 08 Mar 2021 00:27:59 GMT

Anonymous , I hope this is what your expect,

% schedule out of capacity = DIVIDE ( CALCULATE ( SUM ( SCHEDULE[SCHEDULE] ) ), SUMX ( ALLSELECTED ( dMONTH[MTH] ), SUMX ( DISTINCT ( dRole[ROLE] ), CALCULATE ( SUM ( CAPACITY[CAPACITY] ) * DISTINCTCOUNT ( SCHEDULE[EMP_ID] ) ) ) ) )