Notes on Power BI, Fabric and AI.

Power BI

DAX – Dynamic Age Calculation Using Measures

I was recently helping a forum member at https://powerpivotforum.com.au with a problem about how to dynamically calculate an employee’s age.

Read the article

Power BI

Displaying Top N, Bottom N, and “All Others”

If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely. But Using DAX, We Can Just “Split” the Audience into Two Groups.

Read the article

Power BI

DAX – SUM, SUMX or CALCULATE()…Choices, choices!

When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?

Read the article

Power BI

DAX – 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 […]

Read the article

Power BI

DAX – “CONTAINSX” Revisited: What WAS the Match?

Last week’s post on “CONTAINSX” proved to be quite popular. In the comments, Sasha provided an alternate formula that used FILTER instead of SUMX

Read the article

Power BI

DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2

These are a few of my favorite things… Perhaps the only thing that makes me happier than a new “X” function (I still badly want a CONCATENATEX) is “inventing” a new one (like we’ve seen with PRODUCTX).

Read the article

Power BI

“I Modified an Existing Table in Power Query and Now it Won’t Refresh”– A Fix

It’s the same old story, I mashed and twisted some data through Power Query, pulled it through Power Pivot, spent hours creating calculated columns and measures, made a really nice Pivot Table with conditional formatting and all the bells and whistles.

Read the article

Power BI

DAX – Week Ending Date Calculation

The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”. That sounded easy enough… EARLIER() no longer scares me

Read the article

Power BI

DAX – HASONEVALUE vs. ISFILTERED vs. HASONEFILTER

Picking up from Tuesday’s post, which drew some quick comments, let’s add one more function to our evaluation: HASONEFILTER().

Read the article

Power BI

Dynamic TopN Reports via Slicers, Part 2

OK, picking up from Tuesday’s post, with the goal of explaining the techniques. And remember, you can download the workbook here! Two Disconnected Slicers Ah, another favorite technique. Slicer tables […]

Read the article

Power BI

DAX – FILTER() – When, Why, & How to Use It

A recent comment/question alerted me to the fact that I’ve never devoted a post just to this very useful (and often misunderstood) function. Time to correct that.

Read the article

Power BI

DAX – Making the “Case” for SWITCH()

Way back during the period of the first CTP of Gemini (which later became PowerPivot), I was working with a data set that included a column of month numbers

Read the article