Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
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:
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.
VALUE TYPE 2 = IF(AND(Table1[Asset_path]>Table1[Asset_Max_lentgh],Table1[Asset_path]<11),BLANK(),SUM(Table1[VALOR]))
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
hi @tex628
In rows... millions. and if you refer for levels of metadata... only 10 (is the limit in the database)
Ì 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
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)
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! 😕
@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
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 😕
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.
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.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |