Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Dynamic table or on-fly table generation via DAX

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 1Chart 1

 

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

 

Data TableData 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 2Chart 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 DAXTable 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?

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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

 

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.