Using Power BI? You Don't Need DAX Anymore!

Power BI's AI Bot for DAX Measures

In this section, the speaker introduces a new feature in Power BI that allows users to create DAX measures using natural language. The speaker explains how to access the feature and highlights some things to watch out for.

Accessing the AI Bot

• To access the AI bot, go to Quick Measures under Suggestions.

• If it is greyed out, enable it in File > Options and Settings > Preview Features.

• Your admin also needs to enable this in the admin portal under Tenant Settings.

Limitations of the AI Bot

• The "Allow User Data to Leave Their Geography" setting needs to be enabled if you're outside of the US.

• Measure suggestions are based on OpenAI's GPT-3 model.

• The feature is still in preview mode.

Creating Simple DAX Measures with Natural Language

In this section, the speaker demonstrates how to use natural language to create simple DAX measures using Power BI's AI bot.

Creating Total Quantity Measure

• Type "Total Quantity" into the prompt and generate.

• Click "Add" to add it as a measure.

• Update formatting by going with "Whole Number" and adding a thousand separator.

Creating More Complex DAX Measures with Natural Language

In this section, the speaker demonstrates how to use natural language to create more complex DAX measures using Power BI's AI bot.

Generating Total Sales Value Measure

• Type "Total Sales Value" into the prompt and generate.

• This does not work because there is no column that can be summed up directly from sales data table.

• It requires multiplication of price with quantity which has to be done row by row using SUMX function.

• The AI bot cannot generate this measure because it does not know that it needs to multiply "Quantity" with "Price".

• User must have knowledge of DAX functions to create more complex measures.

Generating and Formatting Measures

In this section, the speaker generates measures for total sales and total online sales. They also discuss best practices for formatting measures.

Generating Total Sales Measure

• The "SUMX" function is used to generate the Total Sales measure by multiplying Quantity with Price in the sales table.

• The measure is added to the table and formatted as currency with zero decimal places.

Generating Total Online Sales Measure

• The speaker attempts to generate a measure for Total Online Sales using an existing measure, but it only references the original measure.

• They rename the new measure to "Total Sales for Online Purchase" and regenerate it.

• The formula generated references both the table and column, which goes against best practices since measures are empty shells. It's added to the model, but there's no way to see more of it in preview mode.

• After adding it to the table, they rename it to "Total Online Sales". They also note that when generating measures that use filters, Power BI may add unnecessary code like "KEEPFILTERS". This can be removed if you know what you're doing.

Best Practices for Formatting Measures

• When referencing a source measure in a new calculation, make sure not to reference its original table since measures are empty shells.

• If you move a source measure from one table to another, any calculations that reference it will break unless you remove its original table reference.

• Power BI may add unnecessary code like "KEEPFILTERS" when generating measures that use filters.

Creating Measures Based on Product Type and Location

In this section, the speaker demonstrates how to create a measure based on product type and location.

Generating Total Sales of Innovative Products Outside the United States

• The speaker walks through how to generate a measure for total sales of innovative products outside the United States.

• They note that innovative products are defined by product type in the Product Master Data, while "outside the United States" is defined by both Customer and Store Master Data.

• They use DAX formulas to reference the Total Sales measure, filter by innovative products, and exclude sales made in the United States.

• They also note that this calculation may be slow since it's referencing multiple tables.

Conclusion

In this section, the speaker concludes their discussion on generating measures in Power BI.

• The speaker summarizes what was covered in this video and encourages viewers to experiment with generating their own measures.

Suggested Measures in Power BI

In this section, the speaker discusses the limitations of using suggested measures in Power BI and how to overcome them.

Generating Measures

• When generating a measure, sometimes it suggests the wrong formula.

• If a field is part of a hierarchy and not visible separately, suggested measures cannot see it.

• The speaker demonstrates how unhiding "country" allows the measure to reference "customer country".

• Sometimes suggested measures generate long formulas that are unnecessary.

Editing Measures

• The KEEPFILTERS function is not always necessary and can be removed from generated formulas.

• CALCULATE functions are also not always necessary and can be removed from generated formulas.

• It's important to pay attention to what fields are being referenced in a generated formula as they may not always be correct.

Conclusion

• Suggested measures have limitations and should not replace learning DAX.

• Suggested measures can be useful for procrastinating on writing complex functions or when forgetting specific syntax.