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!)
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
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 are most likely going to want to use the Disconnected Table Trick. There are lots of blog articles about it including:
You likely want to use LOOKUPVALUE, or you can use MAXX(FILTER(…),…)
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.
Take a look at these two Quick Measures as probably want something like them.
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:
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 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.
There are tricks to using RANKX. See these blog articles:
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.
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.
Just want an extra total at the end of your matrix |
|
You want something to be dynamic |
|
You want dynamic row level security (RLS) |
|
You want a slicer to act differently |
|
You want a repeating counter (that resets based upon a condition) |
|
Aggregations over multiple columns |
|
Running/Rolling weeks, months, etc. |
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".
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.