Dynamic table or on-fly table generation via DAX

[Anonymous]

I am stuck with a problem which require me to generate a dynamic table in power bi. I have a fact table which consists of AHT values for different Teams (Supervisor_Name). We want to visualize AHT and number of calls  such that we display 10 equal bins of AHT values in x-axis and the number of calls within the bins on y-axis. the chart should look like the following:

Chart 1

Below is the snapshot of data table that i have referred to:

Data Table

There is another graph that we have created to display Average NTT value by Supervisor name. The size of bubbles represent the call volume by team and position of bubble in the chart is located by Avg NTT value.

Please refer the below graph:

Chart 2

Now we know that both the chart are created from the same table so selection of a bubble in NTT chart would crosfilter the data in AHT line chart.

The logic used in DAX to create bins for selected team is:-

tbl_AHT_Bins =
var min_AHT = 1
var Max_AHT = MAX(AHT[AHT])
Var Interval = DIVIDE((Max_AHT - min_AHT),10)
var Result = CROSSJOIN(GENERATESERIES(Interval,Max_AHT,Interval),ROW("Team",SELECTEDVALUE(AHT[Supervisor_Name],"All Team")))

return

Result

Below is table generated from above DAX:

Table generated from DAX

According to me when i select a team in chart 2, the data table in the background should be filtered and the maximum value in DAX will become maximum for team (supervisor_name) selected. Therefore, team in the table generated from DAX now contains the selected team name in the Column 'Team' and selected team values in column 'Value'.

But the table generated using the above DAX remain static. 

 Can anyone  look into the issue and provide its solution?

[v-yuta-msft · Accepted solution]

Hi satinmalik,

Based on your diagram, you want to create a dynamic calculate table based on the value selected in slicer, right? Unfortunately, dynamic calculate table or dynamic calculate column are not supported by DAX, you should create a chart(e.g.: table chart) and use a measure instead. For specific steps:

Firstly, create a calculate table with modified DAX like below:

tbl_AHT_Bins =
var min_AHT = 1
var Max_AHT = MAX(AHT[AHT])
Var Interval = DIVIDE((Max_AHT - min_AHT),10)
var Result = CROSSJOIN(GENERATESERIES(Interval,Max_AHT,Interval),Selectcolumns(Data, "Team",[Supervisor_Name]))

return

Result

Then, create a measure on Result table using DAX like this:

Result Measure = Calculate(sum('Result'[Value]), 'Result'[Team] = selectedvalue('Result'[Team]))

Hope it's helpful.

Jimmy Tao