Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dobregon
Impactful Individual
Impactful Individual

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)detail.JPG

 

 

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.matrixvisual1.JPG

 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:
    matrixvisual2.JPGKnowing the position of the levels I can 2 different measures 
    matrixvisual3.JPG

 

  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:



    valuetype1.JPG

    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

    valuetype2.JPG

    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
tex628
Community Champion
Community Champion

How large is your original dataset?


Connect on LinkedIn
dobregon
Impactful Individual
Impactful Individual

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)
tex628
Community Champion
Community Champion

Ì 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



Connect on LinkedIn
dobregon
Impactful Individual
Impactful Individual

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)
tex628
Community Champion
Community Champion

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! 😕


Connect on LinkedIn
dobregon
Impactful Individual
Impactful Individual

@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)
tex628
Community Champion
Community Champion

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:

image.png

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


Connect on LinkedIn
dobregon
Impactful Individual
Impactful Individual

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)
Anonymous
Not applicable

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...

 

dobregon
Impactful Individual
Impactful Individual

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.