cancel
Showing results for 
Search instead for 
Did you mean: 

Quick Calculated Table

2046 Views
Highlighted
rsaprano
Advisor

Quick Calculated Table

 

The 'Create Caclulated Table' feature in PBI Desktop is incredibly powerful yet I imagine it is under-used because users need a fairly advanced understanding of DAX to work out the right arguments of the various table functions.

However almost all of the use cases for creating calculated tables are conceptually simple and would be easy for users to stiuplate fields in a wizard/selecting fields in a pane option. This makes them an ideal candidate for implementing in the same way as Quick Measures. In fact I'd go as far as to say that the feature itself should default to a 'Create Quick Calculated Table' wizard - the user could pass in custom DAX if they need it but I believe that this would be seldomly used.

 

Some potential use cases/examples for quick calculated tables include:

 

1) Creating a table consisting of just unique values from another column (e.g for creating a dimension table) - the user selects the field to create the table from and PBI desktop automates the creation of a calculated table e.g. using NewTable=VALUES(Table[SelectedField]).

 

2) Creating a table consisting of all the unique combinations of two or more fields which exist in the data model - the user selects the two or more fields required and PBI desktop automates the creation of the table as CROSSJOIN(VALUES(Table[SelectedField]),VALUES(Table2[SelectedField2])

 

3) Creating a table which is a copy of another table though filtered on particular conditions - the user seleccts the table, the filter field(s) and conditions, and PBI desktop automates the creation of the table as CALCULATETABLE(SelectedTable,SelectedTable[FilterField1] = "Value 1",SelectedTable,SelectedTable[FilterField2] = "Value 2"....)

 

4) Creating a summary table consisting of the same fact table though summarised at a higher level of granularity - the user specifies the group by fields and the new name/aggregated field name/aggregation required for each of the numeric columns. (This would work in a similar way to the 'Group By' feature of the Query Editor). An example for this use case is in the attached PBI Desktop file: DailyFiguresCalc =SUMMARIZE(Readings,Readings[Date],Readings[measure],"Number of Daily Values",COUNTROWS(Readings),"Max Daily Value",MAX(Readings[value]),"Min Daily Value",MIN(Readings[value]),"Average Daily Value",AVERAGE(Readings[value]))