Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Dax Formula with 1:M Relationship

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.

1 ACCEPTED SOLUTION
Community Champion

@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] ) )
)
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
5 REPLIES 5
Community Champion

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

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Anonymous
Not applicable

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.

Community Champion

@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] )
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Anonymous
Not applicable

@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)

Community Champion

@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] ) )
)
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors