Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Removal of blanks from sub row in matrix while...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Removal of blanks from sub row in matrix while maintaining total value

03-08-2019
02:37 PM

Hello community,

Need your help with a following scenario.

I have a data table set where there are blanks in certain columns.

When I create a matrix value, I get the below table with blank rows being displayed in the product type and Product family for Bus Product need category

I want to display the all categories and restrict the blank row display for Product Type and Product Family and still be able to show the complete total value. If I apply filter to remove the blank from Product Type or Product family, it removes bus option completely from Product Need and the total value is also reduced. Any idea how I can limit the blank row in subsequent cateogirs without affecting total value or display on the matrix?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
06:10 PM

**Re: need to create multiple vs. one formula:**

The measure in #4, which I believe provides the output you are looking for, references the measures in #2 and #3. So, I presented it such that, yes, you would need to do multiple formulas to get it working. It can be done in one measure, though. See below.

First, I'll note the formula under #1 is for a calculated column, so to add it to your model, you need to go to the data layer, Modeling tab, and click the New Column button in order to add it; another way would be to right click on the table in data model and click "New column"

If you create the calculated column in the model, it's relatively straightforward to combine the three measures I provided into one if you wanted:

TotalSalesOneMeasure = VAR FilterDepth = ISFILTERED(Products[ProductNeed]) +ISFILTERED(Products[ProductType]) +ISFILTERED(Products[ProductFamily]) VAR MaxDepth = MAX(Products[HierarchyDepth]) RETURN IF( FilterDepth>MaxDepth, BLANK(), SUM(Products[Total Sales]) )

If you *really* want to just put it all in one measure and not bother with a calculated column (this is not as efficient, but it's probably not noticable unless you are building complex measures off of this and/or working with larger datasets):

TotalSalesOneMeasureNoCalcCol = VAR FilterDepth = ISFILTERED(Products[ProductNeed]) +ISFILTERED(Products[ProductType]) +ISFILTERED(Products[ProductFamily]) VAR MaxDepth = MAXX( Products, ISTEXT([ProductNeed]) +ISTEXT([ProductType]) +ISTEXT([ProductFamily]) ) RETURN IF( FilterDepth>MaxDepth, BLANK(), SUM(Products[Total Sales]) )

**Re: error**

My primary guess is that you tried to do the formula in #1 as a measure instead of a calculated column (as that is exactly the error you'd get if you tried to do that). Otherwise, it may be due to a difference between the generalized dataset you provided and your actual dataset (assuming they are different). Or it could simply be a typo-type kind of error - perhaps check the calculated column and measures to see if there are unrecognized references, etc. (although the error doesn't really imply that is the problem).

Hope that helps!

11 REPLIES 11

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
07:16 PM

Hi,

A much simpler way would be to replace Blanks with Unknown in the Query Editor. The end result would be this:

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2019
06:59 PM

Hi Ashish_Mathur,

No, that won't work - if there is no values in the sub hiearchy, I don't want to display unknown.

I only want to limit the sub hiearchy if there is a blank.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
03:31 PM

One technique I use in situations like this is the following:

- Calculate the hierarchy depth for each row in your table (i.e. calculated column)

note this assumes the blank cells are actually blank/null and not empty text ("") - if they are empty text, either make null in PowerQuery or use a different check ( e.g. LEN([column])>0 😞HierarchyDepth =

ISTEXT([ProductNeed])

+ISTEXT([ProductType])

+ISTEXT([ProductFamily]) - Create a measure that calculates the filter depth
FilterDepth =

ISFILTERED(Products[ProductNeed])

+ISFILTERED(Products[ProductType])

+ISFILTERED(Products[ProductFamily]) - Create a measure that calculates max depth per hierarchy
MaxDepth =

MAX(Products[HierarchyDepth]) - Now you can wrap any measure you want in "if the filter depth is greater than the max depth of the current hierarch, then show as blank, otherwise do the measure" - specifically, for your answer
TotalSales = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[Total Sales]) )

Credit to SQLBI, as the above is a stripped down interpretation of their Parent-Child Hierarchies pattern.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
03:58 PM

Hi MarkLaf,

Thanks for the note. Would I need to create three seperate DAX formulas you mentioned or it would all be under one single formula?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
04:15 PM

Having another challenge, when I entered the DAX formula, getting following error message:

"A single value for column ProductNeed in table cannot be determined. This can happen when a measure formula refers to a column that contains many values"

Am I doing something incorrect?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
06:10 PM

**Re: need to create multiple vs. one formula:**

The measure in #4, which I believe provides the output you are looking for, references the measures in #2 and #3. So, I presented it such that, yes, you would need to do multiple formulas to get it working. It can be done in one measure, though. See below.

First, I'll note the formula under #1 is for a calculated column, so to add it to your model, you need to go to the data layer, Modeling tab, and click the New Column button in order to add it; another way would be to right click on the table in data model and click "New column"

If you create the calculated column in the model, it's relatively straightforward to combine the three measures I provided into one if you wanted:

TotalSalesOneMeasure = VAR FilterDepth = ISFILTERED(Products[ProductNeed]) +ISFILTERED(Products[ProductType]) +ISFILTERED(Products[ProductFamily]) VAR MaxDepth = MAX(Products[HierarchyDepth]) RETURN IF( FilterDepth>MaxDepth, BLANK(), SUM(Products[Total Sales]) )

If you *really* want to just put it all in one measure and not bother with a calculated column (this is not as efficient, but it's probably not noticable unless you are building complex measures off of this and/or working with larger datasets):

TotalSalesOneMeasureNoCalcCol = VAR FilterDepth = ISFILTERED(Products[ProductNeed]) +ISFILTERED(Products[ProductType]) +ISFILTERED(Products[ProductFamily]) VAR MaxDepth = MAXX( Products, ISTEXT([ProductNeed]) +ISTEXT([ProductType]) +ISTEXT([ProductFamily]) ) RETURN IF( FilterDepth>MaxDepth, BLANK(), SUM(Products[Total Sales]) )

**Re: error**

My primary guess is that you tried to do the formula in #1 as a measure instead of a calculated column (as that is exactly the error you'd get if you tried to do that). Otherwise, it may be due to a difference between the generalized dataset you provided and your actual dataset (assuming they are different). Or it could simply be a typo-type kind of error - perhaps check the calculated column and measures to see if there are unrecognized references, etc. (although the error doesn't really imply that is the problem).

Hope that helps!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-08-2019
06:24 PM

Hi MarkLaf,

Thanks for your note - I had made an error. For the HiearchyDepth, I had indeed taken it as a new measure than column, hence it wasn't working.

Question: If I am adding multiple columns for value, wouid I need to define for all those columns? In the example we had TotalSales

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2019
01:45 AM

I'm not sure I understand your question, although I attempted an answer below. If it does not address your issue, an example (additional sample data and expected output) of what you want may help.

If you mean that there are other columns in your actual dataset that you either

- want to include in separate measures (e.g. total operating costs in addition to total sales), then simply put your desired measure within the "if the filter depth is greater than the max depth of the current hierarch, then show as blank, otherwise do the measure" logic I referenced in my initial answer. Example:
TotalOpEx = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[Total Op Ex]) )

- OR if you need to sum/multiply/etc. (e.g. units * unit cost) of multiple columns in your table and then show in a visual, then probably the simplest approach is to add a calculated column in the table in your data model that does said addition/multiplication/etc. that you can then reference in a measure with the same "blank if filter depth is greater than hierarchy" logic. Exmaple:
//Calculated column on table in your model CalcSale = [Price] * [Quantity]

//Measure to use in a visual that is referencing calculated column TotalCalcSales = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[CalcSale]) )

In both instances of example code above, I'm referencing the FilterDepth and MaxDepth measures that I described in #2 and #3 of my first response. Copying here again for ease of reference:

FilterDepth = ISFILTERED(Products[ProductNeed]) +ISFILTERED(Products[ProductType]) +ISFILTERED(Products[ProductFamily]) //As before, this is referencing the HierarchyDepth calculated column MaxDepth = MAX(Products[HierarchyDepth])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-09-2019
07:06 PM

Hi MarkLaf,

Apologies if my query was a bit unclear.

Rephrasing it - with below example. In the below table for examply, if I multiple values to display in my matrix for e.g. total sales, number of units sold and ASP, would I need to add all of them in the formula like below:

TotalSales = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[Total Sales])

SUM(Products[Number of Units sold])

SUM(Products[ASP]) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-10-2019
12:15 PM

If all those columns are in your table in the data model, and you want to show them all in a matrix but hiding the blank row headers, then you would need to do a measure for each one.

TotalSales = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[Total Sales]) )

TotalUnits = IF( [FilterDepth]>[MaxDepth], BLANK(), SUM(Products[Number of Units sold]) )

Note that I'm actually calculating ASP in the measure rather than using an average from a calculated column in the table, as that will ensure it is correct regardless of filter context (e.g. correct for Car at summary level and for an individual product family).

TotalASP = IF( [FilterDepth]>[MaxDepth], BLANK(), DIVIDE([TotalSales],[TotalUnits]) )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-09-2019
01:56 AM

I have tried this solution but I have a problem.

I have my Account Hierarchy structure in a seperate table, connected to the fact table using a bridge.

My FilterDebth is always max, I dont know how to get it to give me the correct number when I am expanding my account hierarchy in my P&L statement. I use the matrix visualization.

My formulas:

As expression:

FilterDepth2 =

ISFILTERED(AccountHierarchyFlash[Level 1 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 2 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 3 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 4 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 5 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 6 + Name])

+ISFILTERED(AccountHierarchyFlash[Level 7 + Name])

As measure - correct one:

MaxDepth =

MAX(AccountHierarchyFlash[HierarchyDepth])

Inside my Account table - correct:

As calulated calumn:

HierarchyDepth =

ISTEXT([Level 1 + Name])

+ISTEXT([Level 2 + Name])

+ISTEXT([Level 3 + Name])

+ISTEXT([Level 4 + Name])

+ISTEXT([Level 5 + Name])

+ISTEXT([Level 6 + Name])

+ISTEXT([Level 7 + Name])

Why is my filterdepth always 7 ???

Announcements

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Featured Topics

Top Solution Authors

User | Count |
---|---|

214 | |

53 | |

43 | |

42 | |

42 |

Top Kudoed Authors

User | Count |
---|---|

269 | |

210 | |

73 | |

71 | |

64 |