DAX how to use measure as a table

[Anonymous]

Hi all,

I have two tables, Teams and Games. I want to use Team.Team as a filter (slicer) in a report and every time user changes the Team all calculations should be calculated again.
So, in a games table there is teamid, homegoals, awaygoals and date fields.
One calculation uses the POISSON.DIST(x,mean,cumulative) DAX formula so that I generates a new table called PoissonCalculations and I want to use that table in the source of visualization
Here is the DAX that creates the table

VAR Mean = [AverageGoals] -- average of goals and it is calculated in games table

VAR Vals = GENERATESERIES ( 0, 5, 1 )

VAR CumulativeFalse = FALSE -- Probability density function

RETURN

ADDCOLUMNS (

Vals,

"Poisson Distr.", -- Probability density function

FORMAT ( POISSON.DIST ( [Value], Mean, CumulativeFalse ), "#,0.00000" )

)

Table is created nicely, but when I change the team filter it is not calculated again .... how can I change the DAX to get this PoissonCalculations table to be refreshed with new data .....

Can someone point me how to do this ?

[V-lianl-msft]

Hi @Anonymous ,

The tables and columns in power bi are always static, and only measure can calculate dynamically. Try to split your calculated table and replace those calculations with measure, as @AlexisOlson  said.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Create a fixed table for Vals = { 0, 1, 2, 3, 4, 5 } and do the rest dynamically as measures.

[ValtteriN]

Hi,

Consider placing your table within variable then refer to that variable in measures. This will help the data to update and additionally it will make your data model more optimized. To clarify what I mean here is an example of DAX that uses calculated table as vartable to calculate average of category sums:

Summarized average = var vartable = SUMMARIZE(AverageExample,AverageExample[Category],"Value",SUM(AverageExample[Values])) return

AVERAGEX(vartable,[Value])

Now I can filter this dax with category slicer and the values update:
I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[Anonymous]

Hi,
I'm not sure this works ....
The PoissonCalculations calculated table with GENERATESERIES DAX is like
value     poisson
0           0,002
1           0,22223
2           0,3456
3           0,0001
4           0,000001
5           0,00001

The POISSON.DIST is counted for all those 0-5 values based on each teams Mean value that changes every time ...