Forum Discussion

stan_w_gifford's avatar
stan_w_gifford
Icon for Helper I rankHelper I
1 year ago
Solved

Advice/howto - many to 1 relationship

Good morning/afternoon. I cannot get my head around this so I cast this to the community.   I have two tables Table1 Name      CM        Appointments Joe          Joe2025-01      1 Joe        ...
Need Help
Tips and Tricks
  • stan_w_gifford's avatar
    stan_w_gifford
    1 year ago

    As mentioned the actual measure is;

     

    Leave Days Measure =
    VAR CurrentName = AllSelected(Consolidated2[LeaveKey])
    RETURN
    CALCULATE(
    SUM(Leave[Days_Leave]),
    Leave[LeaveKey] in CurrentName,
    ALL(Consolidated2)
    )
     
    To Get the correct value in the card I made 4 descrete measure (I have 4 salespeople) like.....
     
    AL_Leanne = sumx(Filter(Consolidated2,Consolidated2[Who]="Leanne"),[Leave Days])/Calculate(COUNTROWS(Consolidated2),Consolidated2[Who]="Leanne")
     
    So simplistically, the measure finds the number of days leave between two days via the leave key and uses ALL the descrete LEAVEKEY values exposed via the filter.
     
    Then the measure for each salesperson calculates the leave from the measure sumerised - then divided by the number of entries in Consolidated2 - AKA Table1.
     
    I am going to be marking this response as the correct/Solution - however really some previous answers led me to develop this - so really there are multiple correct solutions...................
     
    Stan
v-dineshya's avatar
v-dineshya
Icon for Community Support rankCommunity Support
1 year ago

Hi stan_w_gifford ,

Thank you for reaching out to the Microsoft Community Forum.

 

Hi jaineshp , Thanks for your prompt response,

 

Hi stan_w_gifford ,  Could you please try the proposed solution shared by jaineshp ,  Please do let us know if you have any further queries.

 

Regards,

Dinesh