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

Reply
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:

ROLEFEB-CAPACITYMAR-CAPACITYAPR-CAPACITYMAY-CAPACITY
 A150172.5157.5165
 C172.5150165172.5
 E157.5165172.5150
 F165157.5150157.5

 

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

 

TB2:

ROLEEMP_IDProject LabelFEB-SCHEDULEMAR-SCHEDULEAPR-SCHEDULEMAY-SCHEDULE
 A1Billable135172.5157.5150
 F3Billable086.25150165
 C8Billable3037.5045
 A14Holiday4552.57.50
 E26Billable801256478
 F47Holiday65105970
 C87Billable3537.54898
 F92Billable59065110
 E98Billable25457085

 

 

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

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

Screenshot 2021-03-08 012204.png


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!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-03-08 012416.png

 


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.

 

 

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

Screenshot 2021-03-07 200752.png


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)

 

 

 

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

Screenshot 2021-03-08 012204.png


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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors