Fully-Customizable DAX-Based Alerts In Power BI And Power Pivot, Our New Class Schedule, And Announcing A Contest!

Fully-Customizable DAX-Based Alerts in Power BI and Power Pivot, Our New Class Schedule, and Announcing a Contest!

This Email and the Highlighted Text were Automatically Generated in Response to DAX “Detectors” Scanning Our Results During Scheduled Refresh I’ve Wanted this Feature Forever.  We Now Have It. Classes Announced for Houston, Los Angeles, Indianapolis, and London First, we…

Read the Rest

So Your Detailed/Flat Pivot is Slow and Doesn’t Sort Properly? Try Text Measures!

Post by Rob Collie

Detailed Pivot Report Using Flattened Pivot

Does Your Pivot Look Like This?  Does its Slow make you Sad?  Time for a Fix!

Tell me if this sounds familiar…

Yes, you know that pivots are meant to show aggregations.  Summaries.  Pivots were NOT invented to display thousands of rows of detail data.

But still, sometimes you need to do precisely that. The biz needs its list of customers and how much they’ve been buying, for instance, and all that data is in YOUR Power Pivot model.

And hey, pivots are really the only game in town* for table-shaped display of data.  So, you build one of the monstrosities like the above.

(*OK yeah, you DO know about this thing called DAX Query Tables, but those are seriously a pain to set up.  So, no.  You rule those out before even starting.  Just like me!

So You Do The Flattened Pivot Dance, Right???

In pictorial form…

Detailed Pivot Report Using Flattened Pivot

The Flattened Pivot Option – Found in the PivotTable Dropdown in the Power Pivot Window

Detailed Pivot Report Using Flattened Pivot

Next, You Pile a Whole Bunch of Fields Onto Rows

Turn Off Subtotals In Your Flattened Pivot

Then, on the PivotTable Design Ribbon Tab, You Turn Off All Subtotals

And Voila!  It’s Slow as Heck.

Read the Rest

The Diabolical Genius of “SWITCH TRUE”

Post by Rob Collie

SWITCH TRUE Alternative to Nested IF's

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.

But There’s a Sneaky Antidote!  We CAN Still Use SWITCH!

Read the Rest