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
stan_w_gifford's avatar
stan_w_gifford
Icon for Helper I rankHelper I
1 year ago

OK - got it to now work in a card.

 

The actual formula is

 

Leave Days Measure =
VAR CurrentName = AllSelected(Consolidated2[LeaveKey])
RETURN
CALCULATE(
SUM(Leave[Days_Leave]),
Leave[LeaveKey] in CurrentName,
ALL(Consolidated2)
)
 
When I put the value in the card, I get the leave across everyone - so to get a specific persons leave over the period I filter the card for say Leane with a leavekey that starts with Leanne.
 
Thanks for the guidance - with combined effort we got there in the end.
 
Stan

 

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi stan_w_gifford ,

    Thank you for your response.  If you have any question relating to the current thread, please do let us know and we will try out best to help you.

     

    Regards,

    Dinesh

  • stan_w_gifford's avatar
    stan_w_gifford
    Icon for Helper I rankHelper I
    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