Before You Post, Read This

Greg_Deckler · ‎05-24-2020

Introduction

There are some common issues/problems that many Power BI users will encounter at some point or another during their course of using Power BI. In this blog article, I attempt to catalog the most common, reoccurring issues that are repeatedly posted to the forums. Before you post, make sure that it isn’t already a known and solved common problem by consulting the list below.

And, looking for input and improvements. Did I miss any common issues/problems? Did I miss any great blog articles that can resolve any of these problems (likely!)

The totals in your table/matrix are wrong

This is almost certainly caused by what is referred to as “the measure totals problem”. This one is extremely common. See this post that explains it, Dealing with Measure Totals

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.

Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.

Or this Quick Measure submission, Table Matrix Totals or Subtotals

You know how to do it in Excel but not in DAX

There is an entire blog article series and project dedicated to how to translate Excel functions to DAX. Here is the home page of the series, Excel to DAX Translation

You want to find the maximum or minimum for something, but you actually want to return a different column/value that is associated with this maximum or minimum

Lookup Min/Max

You want to dynamically switch between measures or you are trying to use a measure but Power BI won’t allow you to use your measure in the way you want, like on the axis of a visualization, etc.

You are most likely going to want to use the Disconnected Table Trick. There are lots of blog articles about it including:

You need a value in an unrelated table

You likely want to use LOOKUPVALUE, or you can use MAXX(FILTER(…),…)

You want to dynamically filter visualizations based upon user interaction, such as selecting things in a slicer:

The Complex Selector

You have a conditional formatting issue:

You are dealing with a duration in DAX in the form of hh : mm : ss.

You will need to convert those to seconds, add them and essentially convert them back more or less. Look at the links below, they should get you what you need.

You have a “start date” and an “end date” and are trying to know how many of something you have on all of the dates between your date intervals.

Take a look at these two Quick Measures as probably want something like them.

You are trying to get year-to-date, previous year-to-date, month-to-date or do something using any of the “time intelligence” DAX functions.

First, if you are trying to use the time intelligence functions in DAX and do not have a separate calendar table, you need to create one using CALENDAR or CALENDARAUTO. Then you would do things like this:

YTD = TOTALYTD(SUM(‘Table’[Column]),ALL(‘Calendar’[Date]))
PYTD = CALCULATE([YTD],SAMPERIODLASTYEAR(‘Calendar’[Date]))

Also, if you are still failing with DAX time intelligence functions or have a specific circumstance that they do not cover or just am tired of the whole magical “black box” that are time intelligence functions that do not have explicit control over, then just do it with filters because that’s all they are, Time Intelligence the Hard Way

You want to perform a calculation between two rows of a table, especially if you are looking for a calculation between the “previous” or “next” row

You are likely going to want to use the EARLIER function or create a variable using VAR that stores the current row’s value and then essentially “lookup” the previous or next row using LOOKUPVALUE or MAXX(FILTER(…),…) or MINX(FILTER(…),…). See this article on Mean Time Between Failure (MTBF) which uses EARLIER.

You are having an issue with RANKX

There are tricks to using RANKX. See these blog articles:

You are having trouble with a Many-To-Many relationship

Try to get rid of your many-to-many relationship by inserting a bridge table of DISTINCT values between the two tables and using the column from your bridge table in your matrix. Another approach is to create a combination key using concatenation of 2 or more columns.

You need the aggregate of a measure

Another common scenario is that you wish to get the average or some other aggregation of a measure for a group of “things”. So think of the circumstance where you are using a measure to calculate something but now you want to know the average or sum or max or min of that measure over some grouping of rows. This is the measure aggregation problem. See the blog article about that here, Design Pattern Groups and Supergroups

The basic pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Check for Other Common/Useful Quick Measures that may apply

Just want an extra total at the end of your matrix	The New Hotness - Custom Matrix Hierarchy
You want something to be dynamic	Dynamic Everything
You want dynamic row level security (RLS)	Dynamic Hierarchical Row Level Security
You want a slicer to act differently	Patient Cohort (AND Slicer) Inverse Selector Inverse Aggregator
You want a repeating counter (that resets based upon a condition)	Cthulhu
Aggregations over multiple columns	MC Aggregations
Running/Rolling weeks, months, etc.	Running Max/Min Average of Running Total Rolling Average Rolling Weeks Rolling Months Running Product Running Total

You have any issue involving a Correlation ID

Most likely the forums will be of little help except to confirm if others are seeing a similar issue. You should check the Issues forum here, Issues Forum.

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Time to post?

If none of these solutions are what you are looking for, it is time to post to the forums. Please see this post regarding How to Get Your Question Answered Quickly.

The most important parts are:

Sample data as text, use the table tool in the editing bar
Expected output from sample data
Explanation in words of how to get from 1. to 2.