topic Re: DAX how to use measure as a table in DAX Commands and Tips

DAX how to use measure as a table

Anonymous — Wed, 05 Jan 2022 14:58:01 GMT

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 ?

Re: DAX how to use measure as a table

ValtteriN — Wed, 05 Jan 2022 15:34:51 GMT

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!

Re: DAX how to use measure as a table

Anonymous — Wed, 05 Jan 2022 17:17:05 GMT

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 ...

Re: DAX how to use measure as a table

AlexisOlson — Wed, 05 Jan 2022 21:34:04 GMT

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

Re: DAX how to use measure as a table

V-lianl-msft — Mon, 10 Jan 2022 02:52:44 GMT

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.