Tag: How to

DAX Fridays! #148: CONVERT and resolve variant data-type error

Posted on by Annie D

Have you got this error in Power BI? “Expressions that yield variant data-type cannot be used to define calculated columns”. If you have, check this video to learn how to fix it using the new DAX function CONVERT.

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to DAX Fridays survey: http://bit.ly/2MMM4KK

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!Here you can download all the pbix files: https://ift.tt/2yGx9Ih

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #SUBSCRIBE

View on YouTube

Upcoming Enterprise DNA November 2019 Learning Summit – What You Will Learn

Posted on by Annie D

FOR MORE DETAILS CLICK HERE – https://ift.tt/2q8z6Ni

Details of what will be covered below

Session 1 – Virtual Table Exploration

– Understand what virtual tables are in Power BI
– Create dynamic data groups within formulas
– Discover advanced insights fast with DAX
– Explore the limitless possibilities with virtual tables

Session 2 – Dynamic Grouping Concepts

– Learn why you should use this analytical technique
– Discover new ways to create groups for your data
– Uncover valuable insights using these ideas
– Group various dimensions from your data model effectively

Session 3 – Ranking Insights Tips

– Learn the versatility of ranking formulas with DAX
– Understand the possibilities with ranking functions
– Delve into the nuances of these important functions
– Create unique visualisations with ranking techniques

Session 4 – Developing Enterprise Apps

– Learn how to build effective app workspaces
– Scale the Power BI online service within your teams
– Manage multiple different reports and insights effectively
– Refresh your enterprise distribution strategies for insights

View on YouTube

7 ways to kill your power bi model -Part 2

Posted on by Annie D

Oh no!! I killed my Power BI model!, but if you read datachant’s blog you already know that 🙂
I killed it in 7 different ways, did you catch them all?

In this video I will show you how to avoid killing Power BI, how is that for a halloween video??

Reference links:

Datachant/ Gil Raviv blog post part 1:https://ift.tt/2NkV5sk

Other links:
Avoid flat tables: https://www.youtube.com/watch?v=QWHE8N3AkT0
Compression in Power BI/ Vertipaq: https://www.youtube.com/watch?v=A461ktvExH0
Calculated columns vs measures: https://www.youtube.com/watch?v=SmXLgEHXSGc
Avoid bi-directional relationships: https://www.youtube.com/watch?v=IlxqDCIuarU
Implicit vs explicit: https://www.youtube.com/watch?v=A95nqPLdmRU

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

3 Amazing Time-Saving Tips Using Excel AUTO-CORRECT

Posted on by Annie D

In this video, I will show you how to use Excel Autocorrect to save time in Excel.

Excel autocorrect is a feature that’s built to correct misspelled words or convert some shortcodes into symbols.

In this video, I cover the following examples:
— Get full-text string by using an abbreviation. For example, when you type “abc”, it can automatically change it to ABC Private Limited
— Insert a symbol as soon as you type a shortcode
— Get a full formula as soon as you use a specific text string (shortcode)

You can read more about Excel autocorrect here: https://ift.tt/2BZk9jm

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube

Connect Power BI with your dataflow storage

Posted on by Annie D

In this video we will go step by step and configure an azure data lake gen 2 as a dataflow storage and connect power bi to it.

Here is the tutorial i followed: https://ift.tt/2JykeyA

Keynotes:
00:57 Create an Azure Data Lake Storage Gen2 storage account
01:50 Where is my Power BI data stored?
02:30 Enable Hierarchical namespace in the dataflow storage account
03:50 Grant the Power BI service a reader role in the dataflow storage account
05:00 Create a file system for Power BI dataflow storage account
07:00 Grant Power BI permissions to the file system storage account
08:00 Connect your Azure Data Lake Storage Gen2 to Power BI
11:00 Configure the Power BI workspace to allow assignment and storage of dataflows in the new storage account

and you are done!!

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

Show Negative Numbers in Red Color (with a Bracket) in Excel

Posted on by Annie D

In this video, I will show you how to show negative numbers in red color and/or with brackets.

This can be done using two methods:
— Conditional Formatting
— Custom Number formatting

Once you decide the format and apply that format (negative numbers showing in red and in bracket), it would automatically be applied to all any changes that happen.

You can read more about this here: https://ift.tt/2hFj5c6

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

TEXTJOIN & TEXT Functions working on Multiple Rows or Columns (DOLLAR Function too) EMT 1614

Posted on by Annie D

Download Excel File: https://ift.tt/34dcAS1
Learn how to join multiple items from different rows and columns into a single cell using an array operation with the Ampersand, the TEXTJOIN Function and the TEXT Function to correctly format the numbers for a single cell text label.
Topics:
1. (00:01) Intro
2. (00:29) Revenue Formula with data from different rows
3. (03:02) Expenses data coming from different columns
4. (03:44) Bonus Formulas
5. (03:30) Summary

View on YouTube

3 Easy Ways to Create a Histogram Chart in Excel

Posted on by Annie D

In this video, I will show you three ways to create a histogram chart in Excel.

The following three methods are covered:
— Using in-built histogram chart option (available in Excel 2016 and later versions)
— Using Data Analysis Toolpak
— Using the FREQUENCY function

A histogram is a chart that you can use to see how many times an event occurs in a given range. For example, you can use it to quickly how many students have scored less than 35 and in 35-50 range, or 50-70 range and so on.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

Share calendars with your organization in Power BI

Posted on by Annie D

Tired of creating calendars every time you are working with a new Power BI file? Do you want to share calendars with your organization, so they dont have to recreate them themselves? Then this is the video for you!

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

Add Secondary Axis in Excel Charts (in a few clicks)

Posted on by Annie D

In this video, I will show you how to quickly add a secondary axis in an Excel Chart.

You can use the recommended chart feature (available in Excel 2013 and later versions). With this feature, it may automatically show you a chart that has a secondary axis. All you have to do is click on that chart and it will be added to the worksheet.

The other way is to manually select the series you want to plot on a secondary axis and select the secondary axis option in the Format Series options.

This video is a part of my ‘Excel is two-minute’ series, where I share simple and quick tips to help you speed up your work and be more efficient

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #Excelin2Minutes

View on YouTube

Quickly Discover Patterns and Trends in Your Data using PivotTables in Excel October 23, 2019

Posted on by Annie D

This is a recording of the October 2019 monthly webinar in the Excel MVP Webinar series. In this free 59-minute webinar, Bill Jelen covers many pivot table techniques:

Table of Contents
(1:36) Traditional Pivot Cache Pivot Tables
(2:45) Setting up data for a successful pivot table
(3:40) Using Alt+Enter for headings
(4:00) Avoiding tiny blank columns between columns
(4:15) Accounting Style Underlines
(5:35) Pivot Table 101 – Your First Pivot Table
(7:55) Changing Number Format in Pivot Tables
(8:35) Moving fields around in a pivot table
(9:40) Drill-down on any pivot table number
(10:45) Refreshing pivot table if the underlying data changes
(11:25) Year-over-Year Pivot Tables – Grouping Dates
(14:05) Adding a formula outside the pivot table and problems with GetPivotData
(16:15) Variance percentage with % Change From Previous
(18:00) Top 10 Pivot Table – Introduction
(18:15) Changing heading from Sum of Revenue
(18:45) Changing the calculation in a pivot table
(19:10) Double-click a field heading to open Field Settings dialog
(19:20) Percentage of Column Total
(19:40) Rank column in a pivot table
(20:00) Running Total in a pivot table
(20:35) Filtering to Top 10
(21:45) Grand Total seems to be wrong after filtering
(22:10) Include Filtered Items in Totals is greyed out
(22:55) Dan in Philadelphia has a solution
(23:25) AutoFiltering a Pivot Table using a Hack
(25:30) Filtering using the Filter drop-zone
(25:45) The pain of (Multiple Items) header and Shrink to Fit
(26:25) Replacing Filter area with Slicers
(26:45) Resizing slicers
(27:30) Replicate a pivot table for each customer (Szilvia)
(28:50) Tabular form instead of Compact Form
(29:55) Suggesting changes to the Excel team with Excel.UserVoice.Com
(30:20) Defaults for all future pivot tables
(31:30) Data Model Pivot Tables – Introduction to Part 2 of Webinar
(32:30) Mash up data from multiple sheets into one pivot tables using the Data Model
(35:55) Count Distinct in a pivot table
(37:35) True Top 10 Report With Correct Total without any hacks
(39:30) Median in a Pivot Table using DAX formula
(41:35) Showing Text in the values area of a Pivot Table
(45:25) One set of Slicers for two different data sets
(48:25) Create a Pivot Table on a Map using 3D Map in Excel
(52:10) Is there a use for GetPivotData?
(57:20) Recap
(58:49) Pitch for next month’s Liam Bastick webinar

View on YouTube

Calculate AGE in Excel from Date of Birth (in Years, Months, & Days)

Posted on by Annie D

In this video, I will show you how to calculate age in Excel in Years, Months, and Days (when you have the date of Birth).

This can easily be done using the DATEDIF function, which is a legacy function and is made available for compatibility reasons with LOTUS 123.

With Datedif you can calculate:
— Total years that have passed in between the date of birth and current date
— Total number of months that have passed in between the date of birth and current date
— Total number of days that have passed in between the date of birth and current date

You can also read more about this here: https://ift.tt/32Ryp9y

Subscribe to this YouTube channel to get updates on Excel Tips and Excel Tutorials videos – https://www.youtube.com/c/trumpexcel

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel. It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

You can find a lot of useful Excel resources on the following site: https://trumpexcel.com/

#Excel #ExcelTips #ExcelTutorial

View on YouTube