Evening people,
This is somewhat of a follow up to a previous post here (http://community.powerbi.com/t5/Desktop/Taking-data-from-two-identical-slicers/m-p/401533), and it's working nicely.
For those that don't want to look back at that, I'm modelling sports results, and I've got two measures which tell me, for a single given game between two players, what the probability of the player that's starting the game wins that game. For later simplicity, I've then put that into three measures that tell me the result of any pair of games where each player starts one game each - i.e. I've got a measure that calculates player A winning both games, the same for player B winning both games, and a third one where they win a game each, call them [A 2-0], [B 2-0] and [1-1]
I eventually need to model this for long series of games. I originally thought to just create a series of measures that have this general format:
[Player A points Player B points] = [Player A points-2 Player B points]*[A 2-0] + [Player A points-1 Player B points-1]*[1-1] + [Player A points Player B points-2]*[B 2-0]
and then for a given game length, sum up all the relevant measures. This didn't work at all and either said the model was too complex, or it threw up a locking error.
I then thought to try to just write one measure, and to have all the intermediate steps as variables. This works much more efficiently (well, it works at least), my questions are more how I can keep things efficient once I expand the model to simulate much longer series of games. I'm primarily wanting to know:
- Is there any limit to the number of variables that Power BI/DAX can define in any given measure?
- The italicised code that I'm currently using is looking at measures for each of [A 2-0], [1-1] and [B 2-0], am I right in thinking that for each individual step that Power BI will recalculate the same measure again and again, and would there be a big advantage in calling the results in as a variable at the start of the overall measure so it only needs to calculate each of these once and then refer to that result continually as it works out all possible results?
- At present, the measure I have is hard coded to look at just one particular length of match which is very common in the sport I'm looking at. I don't really want to have a bunch of visuals looking at every possible length of match, so I assume it would be possible in the return section of the overall variable for it to look at a slicer that gives possible match formats, and then return the result based on the slicer selection?
Cheers peoples
