cancel
Showing results for
Did you mean:
Post Prodigy

## Matrix Visual Metadata Levels need to ignore blanks

Hi,

I have a problem in matrix visual and i hope maybe someone can help me on this.

In this example I have 2 markets called (Zara and Zara Home) and they have different locations. I have created 10 leves of location and a market can have 1, 2, etc levels depends or the details (picure below and only the 01/01/2019 but the example has value from Dec 2018 to Jan 2019)

So, you can see that the 2 markets doesn't have all the levels because they don't need. So, I need to have the sum of VALOR and do the matrix visual (picture below) and then i will have the values by all the metadata and dates.

The problem here is that we can see all the levels (including blanks) and I only want to see the levels with names. For that, i have tried (but these don't solve all the problems).

• First i have created 2 measures in order to know the number of the level and the maximum levels that the market has.
```Asset_Max_lentgh = value(max(Table1[PATHLENGH]))

Asset_path = ISFILTERED(Table1[LEVEL 1])+ISFILTERED(Table1[LEVEL 2])+ISFILTERED(Table1[LEVEL 3])+ISFILTERED(Table1[LEVEL 4])+ISFILTERED(Table1[LEVEL 5])+ISFILTERED(Table1[LEVEL 6])+ISFILTERED(Table1[LEVEL 7])+ISFILTERED(Table1[LEVEL 8])+ISFILTERED(Table1[LEVEL 9])+ISFILTERED(Table1[LEVEL 10])+ISFILTERED(Table1[TIENDA])```
And the result of this:
Knowing the position of the levels I can 2 different measures

1. VALUE TYPE 1
`VALUE TYPE 1 = IF(Table1[Asset_path]>Table1[Asset_Max_lentgh],BLANK(),sum(Table1[VALOR]))`
In this case, i'm calculating the value only in the leves that the asset path is less or equal to the max lenthg. Result:

So, as you see the system automaticaly ignore the leves that doesn't have value. I lost the name, but it can solve it puting in the next level of the last level that the market has a text.
But the big problem is that here i can see the date hierarchy becasue the visual remove the next levels.

2.  VALUE TYPE 2
`VALUE TYPE 2 = IF(AND(Table1[Asset_path]>Table1[Asset_Max_lentgh],Table1[Asset_path]<11),BLANK(),SUM(Table1[VALOR]))`

Here I'm doing a mixure. I need to have the value in the levels that they have name and apart of that need the values in the other metatada apart of levels (tienda, dates, etc) and the result is

But the visual doesn't remove the levels in blank as the Visual for Value Type 1. It seems that the visual remove the blank rows only if there aren't exist any row with values in the bottom.

Can someone help me on this?

*Apart of that i can't upload a file pbi, i don't know why

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
10 REPLIES 10
Super User II

How large is your original dataset?

Post Prodigy

hi @tex628

In rows... millions. and if you refer for levels of metadata... only 10 (is the limit in the database)

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Super User II

Ì dont think it's possible to do what your aiming for, but im not entirely certain.

This might be close to a solution but i'm not sure it's possible with the size of your dataset.

If you in the query loop through your columns to identify which are empty and which hold a value you can place "ZARA" in the highest empty column in the table. In this case that would mean that you place the value "ZARA" in the Level 6 column, since it's lacking a value.

This might not work with how u want it, but its the closest that i can think of

Post Prodigy

Yes @tex628, i did something similar, but the problem is that the levels in blank can have the name of the market or blank. but the problem in the visual is the repeteated blanks or same name that i need to remove.

In reaility in the measure VALUE TYPE 1 i have solved this problem, but the system will not show the other rows like (year, month)

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Super User II

Do this:

Create calculated columns in powerquery and replace the Levels that you currently have:

`Level 1_ = if [Level 1] <> null [Level 1] else [TIENDA]`
```Level 2_ = if [Level 1_] = [TIENDA] then null
else if [Level 2] <> null then [Level 2] else [TIENDA]```
```Level 3_ = if [Level 2_] = [TIENDA] or [Level 2_] = null then null
else if [Level 3] <> null then [Level 3] else [TIENDA]```

Do this all the way upp to Level 11!

I hope u understand how im thinking 🙂

I dont think you can ignore steps in a heirarchy when you have values further down, thats why i dosnt think you can get your VALUE TYPE measure to work! 😕

Post Prodigy

@tex628 yes, this is that what i did in reality. the problem is not the levels. when i put the visual for tienda it is ok, the problem is when i need to put the same levels and down the dates or another attribute that i will hav ein the future. As the visual will not ignore the blanks becasue there will be values after levels..

It is a big problem, thanks for your time

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Super User II

I have a kind of a hard time understanding exactly what you're saying.

But if you're doing what im proposing you will have no blanks before "ZARA"

This is how i want the result to look:

I'm sorry, i'm not sure that I'm understanding you correctly 😕

Post Prodigy

Yes, you are right, but the problem is not the Levels. the problem is when I need to put something more in the rows visual like Dates, or any metadata that i will have in the future.

I have tried a lot of things and i think that the only solutions is to create more levels taking the info from metadata... but the problem that it is not dinamic and need to create columns levels.

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Helper V

Hi @dobregon  Did you find solution ? I'm facing similar issue. Unnecessarily too many blank rows.

Hierarchy Slicer has this functionality to ignore empty levels.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38820319-empty-leaves-on-off-in-m...

Post Prodigy

Yes, i did something similar, but the problem is that the levels in blank can have the name of the market or blank. but the problem in the visual is the repeteated blanks or same name that i need to remove.

In reaility in the measure VALUE TYPE 1 i have solved this problem, but the system will not show the other rows like (year, month)

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.