skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Quick Calculated Table

    Quick Calculated Table

    06-17-2017 11:18 AM

    rsaprano
    MVP
    4497 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    rsaprano
    rsaprano
    MVP
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Quick Calculated Table

    ‎06-17-2017 11:18 AM

     

    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]))

    eyJrIjoiMmJlOGVkYjItMjY2OC00M2VjLTg3ZGQtNmRjNGUxNmEyNmYwIiwidCI6IjBjNzk5ZDM4LTQ3NjQtNDJiYy1iNGZmLTIzYmViYTljN2ZlMiIsImMiOjh9

    Preview file
    83 KB
    EnvAgency Dashboard New Design v2.pbix
    Labels:
    • Labels:
    • Filters
    • Mathematical
    • Other
    • Totals
    Message 1 of 1
    4,497 Views
    2
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices