Hi, I have set up a table that highlights the number of votes a club can cast based on min and maximum number of members. Simple table that consists of Index, Min, Max, Eligible Votes.
How can I link the eligible number of votes to the club membership numbers held in a separate table?
I’m having trouble handling the min / max range . If a clubs membership is between > min & < max, then x number of votes can be allocated.
If I understand your situation correctly, there’s no need to link your described supporting table to your data model. Sounds like a textbook dynamic segmentation problem that Sam has addressed in a number of videos. Here are just a couple that should be directly relevant and very helpful:
https://forum.enterprisedna.co/t/grouping-segmenting-your-data-with-dax-measure-logic/588
https://forum.enterprisedna.co/t/detailed-banding-segmenting-example-in-power-bi-using-dax/584
If you have any problems after taking a look at these please just post your PBIX file and I’ll be glad to work through the issues with you.
Thanks Brian, I have tried the approach suggested and dont quite have the correct answer. Attached is the PBIX file.
What I’m trying to achieve is allocate votes to member clubs based on their total membership. As membership numbers change the number of votes change.
Any help appreciated.NZSFC.pbix (929.2 KB)
After reviewing your PBIX file and getting a better handle on the issue, I decided to take a left turn and base the measure on a simpler SWITCH ( TRUE() ) structure:
Allocated Votes =
// These lookup statements are here to allow you to change the Min/Max
// boundaries in the Club Votes table w/o changing this measure.
// If those boundaries are unlikely to change, you can delete all the
// VARs and hardwire the min/max values into the SWITCH statement to
// simplify the measure and improve performance
VAR Group1Min = LOOKUPVALUE('Club Votes'[Min], 'Club Votes'[Index], 1)
VAR Group1Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 1)
VAR Group2Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 2)
VAR Group3Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 3)
VAR Group4Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 4)
VAR Group5Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 5)
VAR Group6Max = LOOKUPVALUE('Club Votes'[Max], 'Club Votes'[Index], 6)
RETURN
SWITCH( TRUE(),
[Total Membership] < Group1Min, 0,
[Total Membership] < Group1Max, 1,
[Total Membership] < Group2Max, 2,
[Total Membership] < Group3Max, 3,
[Total Membership] < Group4Max, 4,
[Total Membership] < Group5Max, 5,
[Total Membership] < Group6Max, 6,
10000000 //Error trapping to alert you w/ crazy value if [Total Membership] exceeds Group6Max
)
Here are the results:
I think this meets your requirements, but please give a shout if I’ve misinterpreted and it doesn’t.
Full solution file attached below. Hope this is helpful.
