## Automated Testing With Power Query

This is a quick tip on automated testing with Power Query. I loved Nar's post on Automated Testing using DAX. I especially like the rule of always including controls so that business readers can share responsibility for data quality. For…

## The Diabolical Genius of “SWITCH TRUE”

Post by Rob Collie

Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit?  We’re IN!

### An End to Nested IF’s?  Sign Us Up!

When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.

Whereas we used to have to write nested IF’s, such as this:

IF([MyMeasure]=1,expr1,
IF([MyMeasure]=2,expr2,
IF([MyMeasure]=3,expr3,…)))

Now , with SWITCH, we could write that much more cleanly as:

SWITCH([MyMeasure],1,expr1,2,expr2,3,expr3…)

Which do you prefer?  It’s easy to make a strong “case for SWITCH,” isn’t it?

### But What About Cases Other than Equals?

Now, let’s consider the following nested IF:

IF([MyMeasure]<1,expr1,
IF([MyMeasure]<2,expr2,
IF([MyMeasure]<3,expr3,…)))

Notice that we’ve swapped out “=” for “<”.

And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)

This is unfortunate, because in these cases, we’ve had to keep using nested IF’s.  And wow do I (Rob) *hate* nested IF’s.  I can never seem to match the parentheses up correctly on the first try.

## SUMX of IF: A Perfect Blend of Simple & Sophisticated

In This Case, Getting the Grand Total Correct for Each Row Required SUMX

### It’s that time of year again…

…when my love of spreadsheets actually translates into a love of sports.  Yes, it’s Compulsive Data Crunching Disease season, AKA Fantasy Football Season.

Fantasy football is a game in which the contestants assemble “portfolios” of NFL players in the same manner that you might build a portfolio of stocks and bonds.  Then your portfolio (we call it a “team”) performs well if the real-life NFL players perform well, and poorly if not.  The one difference between this and the stock market is that no two “portfolios” can contain the same NFL player – so if I get Peyton Manning, the other contestants in my league (typically 8-12 people) cannot have him.

I’m participating in a new form of league this year, one in which the contestants get to keep some of the players from prior years.  (In most fantasy football leagues, you start each year from a clean slate).

We’re going to be picking our players this weekend at an “auction” or “draft,” and naturally, I want to scout my opponents ahead of time.  Muhaha.

### So, what do my opponents need?

A valid portfolio consists of: