Calculate Max of multiple measures in Power BI

In this post I'll show you how to calculate the maximum of multiple measures in Power BI.

First you need to create the several measures or variable within a measures. 

Measure1 = Sum ( ProductA[Stock] )

Measure2 = Sum ( ProductB[Stock] )

Measure3 = ...

After all your measures are created, create a new one which will contain a list of all measures that you want to evaluate

Measure = 
VAR _List =  List { Measure1, Measure2, Measure3,... }

RETURN

MAXX ( _List, [Value] )

This will return the Max value from all measures included in the _List variable.

Quick SQL Cheatsheet

I found the excellent GitHub repository with all relevant SQL queries and some examples on how to use them. Really useful as a side note to all those who are learning SQL.

https://github.com/enochtangg/quick-SQL-cheatsheet/

Make a good use of it!

How to Find Excel Phantom Links - Part 2

Some time ago I've written about some techniques to eliminate Phantom Links in Excel.
Well, the steps described in my previous article - Here - had always solved my problems... until 2 days ago!

While I was updating a workbook, I had to change a database source from 2016 to 2017 (different Excel file but with same structure), so I just clicked the Edit Links button and that Change Source. As soon as Excel had changed all the links to the 2017 database file, I unexpectedly realized that a link to the 2016 database file was still showing in the Edit Links dialog box!
I've tried every single tactic mentioned on my previous article but I just couldn't find and kill the "Phantom link". I was exchanging some thoughts with a colleague who mentioned a couple more tactics that could solve the problem.

• Search for formulas referencing external workbooks:
  Right-click on any sheet tab >> left-click Select All Sheets >> press Ctrl+F and in the Find What field enter  =[    or  !
  
  This will look through all sheets for formulas referencing external workbooks. You have assure that there are no hidden sheets.

• Conditional formatting formulas:
  Go in to Conditional Formatting >> Manage Rules  >> Show Formatting Rule for: this worksheet and remove the rules which refer to the external link

How to create an interactive chart in Excel

Every advanced Excel user knows that a great report has to be as dynamic as possible in order to avoid many manual changes every time you want to see or add new information to your report.
The following video is a  great tutorial of how to create an interactive chart in Excel.

Enjoy and share!

Excel Tip: Enter the same data in multiple cells at once

How to enter the same data into multiple cells of your worksheet at once ?

If you want to enter the same values into multiple cells of your worksheet at once, you can enter it into one and copy it into each of the other cells, however a quicker solution allows you to enter the same thing into all of those cells at once.

Just take the following steps:

• Highlight all of the relevant cells (you can use the mouse to highlight a range, or click on each of the individual cells while holding down the Ctrl key)
• While they are all highlighted, type what you want to enter in all cells and...
• Instead of hitting Enter, hit Ctrl + Enter
• The information will now appear in all of the selected cells.

That's it.


Do you like this free website? Please share this page on Google+ or Facebook.

Retrieve the value from every Nth row in Excel

Imagine that you want to retrieve the values from every 5th row in a certain range of your Excel worksheet.


In case your range is placed on column A:

1. Edit the first cell on the in the column where you want to have the list of every 5th row of column A
2. Write the following formula.
   
   =IF(ROW(A1)*5-5>ROWS($A$4:$A$50),"",INDEX(A$4:A$50,ROW(A1)*5-5))
    
3. Copy the formula all the way down to as many cells as your range rows divided by 5.
4. There you have it.

Note: This example shows you how to retrieve values from every 5th row in Excel but you can do this for any number. To do so just replace the 5-5 by the number you want.

Now let's break down the formula:

The formula starts with a IF function which will test if  ROW(A1)*5-5>ROWS($A$4:$A$50)

Basically this test will avoid a #REF error in case you copy the formula down to more rows than your range divided by 5. If you do, the formula will just retrieve a blank cell.

In case ROW(A1)*5-5>ROWS($A$4:$A$50) is False, then the following condition will be applied:

INDEX(A$4:A$50,ROW(A1)*5-5)

The index function will grab your range A1:A50 and retrieve the values from the following lines:

ROW(A1)*5-5  → 1*5-5 = line 1

ROW(A2)*5-5  → 2*5-5 = line 5

ROW(A3)*5-5  → 3*5-5 = line 10

...

ROW(A11)*5-5  → 11*5-5 = line 50

Do you like this free website? Please share this page on Google+ or Facebook.

How to Find Excel Phanton Links

Why can't I break a external link in my workbook ? How can I locate "phantom links" in a workbook ?
Excel is asking if I want to update links, but I don't have any external link in my workbook! Is this a bug ?

These are only three examples of questions asked by lots of Excel users when they encounter the infamous "phantom link" phenomenon. Since Excel is never wrong about identifying links, you can be sure that your workbook does contain one or more links... but most probably they are not formula links.

So, how can we identify and eliminate "Phantom links" ?

We are already assuming that you had We can divide the process into 4 steps ... although you might not need them all to break the links in your workbook.

Before starting the execution of the following steps, please assure that you have already hit the Data > Edit Links > Break Link button, which will eliminate all references to external workbooks (except the one(s) which is causing the so called phantom links) .

If the Break Link button has not eliminated all the links to external workbooks, then:

Step 1: Identify Formula Links At this stage the the Edit Links button will most probably not be active anymore. If it is still active then click it and the Links dialog box will open. There you can see the name of the source file(s) for the link(s). Click the Change Source button and change the link so it refers to the active file.

Step 2: Identify links in Defined Names

This second step is, by far, the most common cause and will solve most cases of phantom links. Open your Name Manager (Ctrl + F3 or go to Formulas Tab / Defined Names / Name Manager) and scroll through the list of defined names and examine the Refers To field. If a name refers to another workbook or contains an erroneous reference such as #REF!, delete it.


Step 3: Identify links in Charts This is probably the most "painful" step of them all as you have to click on each data series, text box or title of your chart and examine the formula bar for references to other workbooks. The links can be eliminated by copying (as values) the data located into the current workbook or moving the chart's data to the current workbook and redo the chart.


Step 4: Identify links in Objects External references can also be attached to objects. The quickest way of reviews objects in a workbook is to use the Go To (F5 function key as shortcut) >> Special >> Select Objects >> Click OK. 

 After that, you can press the Tab key to move from object to object and examine the formula bar to search for external references.  If an object contains a reference to another workbook just edit or delete the reference.

Important Note: If Step 1 and Step 2 can be executed for the whole workbook, the last two steps have to be executed on each sheet of your workbook.

Do you like this free website? Please share this page on Google+ or Facebook.