Thanks a lot for a this solution! A piece of advice for everyone who watched this and wondered how to process multiple levels of the hierarchy in a single measure - just add as many rows as you need in between TRUE() and ELSE clause. E.g.: Remove Blanks = SWITCH(TRUE(), AND(ISINSCOPE(hier[Level 2]),ISBLANK(VALUES(hier[Level 2]))),BLANK(), AND(ISINSCOPE(hier[Level 3]),ISBLANK(VALUES(hier[Level 3]))),BLANK(), AND(ISINSCOPE(hier[Level 4]),ISBLANK(VALUES(hier[Level 4]))),BLANK(), 1) A question for you, Ruth - is it possible to hide this measure from the Matrix view? It does it job pretty nicely but has no other purpose than this, so it would be cool to hide it.
I guess there is no need to add multiple rows... U can add the top level from where the blank starts and it will work. This workaround worked for me... But still struggling with that column hiding issue... Since I am showing months in columns it is showing me 12 times the same values.
@@vivekkumarsingh4974 not sure what you mean by adding top level where blank starts... do u mean adding into the measure like he wrote above? it didn't work for me. you have to add all levels to remove the blanks.
Good solution, thank you. One doubt, and if instead of being the last level it is an intermediate one? For example: Project L1 | Project L2 | Task L3 | Task L4 Project1 | | Task100 | Task 101 Thank you
Great video thanks. How would you approach this if you have data in ten year columns rather than just a single column? Would each year needs its own measure?
Thanks for the video! I have a question, though. Is this example, you only have one level of a hierarchy with nulls. What would you do if you had a ragged hierarchy with, say, 10 levels with nulls beginning to appear at level 4? What if your rows with your "null" values actually have data associated with them?
I just added the statement for each level at the top in descending order. Adds a lot of lines but works perfectly SWITCH( TRUE( ), AND( ISINSCOPE( 'dim PNL Element'[Level 3 Name] ), ISBLANK( VALUES( 'dim PNL Element'[Level 3 Name] ) ) ), BLANK( ), AND( ISINSCOPE( 'dim PNL Element'[Level 2 Name] ), ISBLANK( VALUES( 'dim PNL Element'[Level 2 Name] ) ) ), BLANK( ),
Nice clear example as usual, Thanks. Would this condition need to be baked into every measure? With 2 x 9 level hierarchies and 80 measures, is that going to kill performance? The 3rd party Hierarchy slicer has a property to hide empty measures, Ideally this should be a model setting?
Awesome video. One question, I see it works if the lowest level is blank. My data's hierarchy has a list of category '1's. Only 1 of those categories has a sub category (level 2), but all have values in level 3. Therefore, the blanks occur on level 2. How would you handle that?
Brilliant as usual Ruth. I just had an exact same use case for this, and was basically able to replicate your steps 1 for 1. It worked, however I don't get the extra pluses for the Blank values, so it seems this issue was updated.
hey! greetings from Colombia :D thank you so much for your help, I have been looking for a solution on how to hide empty rows and this was very helpful!!!
Hi there, I don't suppose you have a solution if there are blank values in every level of your hierarchy? this works perfectly if you have only one level of blanks, but when doing a separate measure for each level the blanks all reappear...
Hi , how are you , thanks for the video , I have a question , is that applicable if the blank is in an intermediate level , I can see that level 4 in the example is the last level
Hi Ruth, This is what I was looking for. Thanks for the video. But When I try to export the visual output to excel, the excel is blank and has only column titles. No data exported. Can you help me fix this?
Місяць тому
I have the problem wit h the decomposition tree, do you know, how can i remove the blanks from the decomposotion tree. I cant hide the blanks
Thank you, this was a big help. I had a switch() measure that had a format in it and i was having trouble figuring out why it was always non-blank... turns out it was when the format statement was used!
This doesn't seem to work in 2024 unfortunately. Can you do an updated tutorial for Dax105 and this one? (112). It seems that PowerBI recognizes Null in the powerquery editor but doesn't recognize it outside of the editor?
But I'm facing one issue when I'm adding values from another table like add sales from other table which have relationship with it blank space reappearing
SWITCH(TRUE(), AND(ISINSCOPE(hier[Level 2]),ISBLANK(VALUES(hier[Level 2]))),BLANK(), AND(ISINSCOPE(hier[Level 3]),ISBLANK(VALUES(hier[Level 3]))),BLANK(), AND(ISINSCOPE(hier[Level 4]),ISBLANK(VALUES(hier[Level 4]))),BLANK(), 1) This scenario is not working for me, I have a last level which is always there, how to handle this, any help would be great!
Hi, Thanks a lot, Could you please let me know how show parent values on parent instead of aggregation of child values? I used your code to remove Blank and add Selectedvalue() function in Else part of Switch. It works but some parents have value others not. and when I filter the child, Its parent's value changes to child's value.
Imagine I have some Cost or Profit Colum in my original flatten table for each task? Is there a way to use this workaround and also display my profit or costs right next to the hierarchy for the corresponding node without having blanks?
Very Helpful ! please cann you explain me haw i can use this with Power BI Connected directly with my azure Analysis Services ?. my data is in ssdt 2019. i didn't found the best way to remove the blank . because i could'nt use power Query Editor.
thx for your answer, please cann you explain it well for me . i am the one who have to do it. we dont have a Backend Team. do i have do to it in my SQL Server ?
Thanks! I've implemented this trick successfully. However, I need to display a text value instead of a numeric value, I need to be the value of the last level of the hierarchy. Can someone help me? I can't find a workaround. Thanks!
Hi, this is a great solution but have anyone maybe found some workaround or a solution to these (+) icons other than just removing them? I would really like to have them working properly in this situation.
I have dataset in which I would like to kill blanks in top levels of my hierarchy, perfectly to not display level of hierarchy which are having blanks. Is there a way to do this with Isinscope? Thank you!
Thanks a lot for a this solution! A piece of advice for everyone who watched this and wondered how to process multiple levels of the hierarchy in a single measure - just add as many rows as you need in between TRUE() and ELSE clause. E.g.:
Remove Blanks =
SWITCH(TRUE(),
AND(ISINSCOPE(hier[Level 2]),ISBLANK(VALUES(hier[Level 2]))),BLANK(),
AND(ISINSCOPE(hier[Level 3]),ISBLANK(VALUES(hier[Level 3]))),BLANK(),
AND(ISINSCOPE(hier[Level 4]),ISBLANK(VALUES(hier[Level 4]))),BLANK(),
1)
A question for you, Ruth - is it possible to hide this measure from the Matrix view? It does it job pretty nicely but has no other purpose than this, so it would be cool to hide it.
you can add measure you need on the last step, where you put "1" - and no need to hide anything
I guess there is no need to add multiple rows... U can add the top level from where the blank starts and it will work. This workaround worked for me... But still struggling with that column hiding issue... Since I am showing months in columns it is showing me 12 times the same values.
thank you so much, that's the problem I was dealing with
@@vivekkumarsingh4974 not sure what you mean by adding top level where blank starts... do u mean adding into the measure like he wrote above? it didn't work for me. you have to add all levels to remove the blanks.
Good solution, thank you. One doubt, and if instead of being the last level it is an intermediate one?
For example:
Project L1 | Project L2 | Task L3 | Task L4
Project1 | | Task100 | Task 101
Thank you
Very Helpful! I love how you broke down your thought process into easy to follow steps. Thank you!
That is how I do all my DAX :)
Thanks for the feedback!
/Ruth
Great video thanks. How would you approach this if you have data in ten year columns rather than just a single column? Would each year needs its own measure?
This is great .. If I wanted to use the hierarchy in a slicer, how do I get the blanks to not show up there?
i can't believe this video is around since jan 2019....Awesome video Ruth! Thank you so much!!
😊
Hi, Very Useful Video, I was wondering if we have more than 4 level and every level has some blanks. Then how will we deal with the situation.
Thanks for the video! I have a question, though. Is this example, you only have one level of a hierarchy with nulls. What would you do if you had a ragged hierarchy with, say, 10 levels with nulls beginning to appear at level 4? What if your rows with your "null" values actually have data associated with them?
@
StonewallSharpeson have you found a decision to this problem with ragged hierarchy?
No, we just try to train our users around it. I have done quite a bit of research, and found no good, dynamic way to deal with a ragged hierarchy.
No, we just try to train our users around it. I have done quite a bit of research, and found no good, dynamic way to deal with a ragged hierarchy.
I just added the statement for each level at the top in descending order. Adds a lot of lines but works perfectly
SWITCH(
TRUE( ),
AND(
ISINSCOPE( 'dim PNL Element'[Level 3 Name] ),
ISBLANK( VALUES( 'dim PNL Element'[Level 3 Name] ) )
),
BLANK( ),
AND(
ISINSCOPE( 'dim PNL Element'[Level 2 Name] ),
ISBLANK( VALUES( 'dim PNL Element'[Level 2 Name] ) )
),
BLANK( ),
Did anyone got the solution for this ? I have 10 levels of hierarchy
Nice clear example as usual, Thanks.
Would this condition need to be baked into every measure?
With 2 x 9 level hierarchies and 80 measures, is that going to kill performance?
The 3rd party Hierarchy slicer has a property to hide empty measures, Ideally this should be a model setting?
I rarely comment anything but Ruth this is amazing! Thanks for sharing!
Yey! Great that it was useful :)
Awesome video. One question, I see it works if the lowest level is blank. My data's hierarchy has a list of category '1's. Only 1 of those categories has a sub category (level 2), but all have values in level 3. Therefore, the blanks occur on level 2. How would you handle that?
Niceeee, simple and straightforward...👍
wow! your videos are amazing! What if I have multiple (like a 50 level hierarchy) that I want to program to check for blanks?
thanks so much for this video! @curbal - any idea how to implement this same fix within a slicer?
Brilliant as usual Ruth. I just had an exact same use case for this, and was basically able to replicate your steps 1 for 1. It worked, however I don't get the extra pluses for the Blank values, so it seems this issue was updated.
Great, thanks for sharing :)
thanks once more Ruth! Not sure how I missed this one, but it's 10/10! I hope all is well!! Oliver
Great! Hope you are doing great too :)
hey! greetings from Colombia :D thank you so much for your help, I have been looking for a solution on how to hide empty rows and this was very helpful!!!
Genius. What I wouldn't give to have stumbled upon this video 2 weeks ago.
Better late than never hopefully!
Thanks a lot for the wonderful solution. Love from India.
Hi there, I don't suppose you have a solution if there are blank values in every level of your hierarchy? this works perfectly if you have only one level of blanks, but when doing a separate measure for each level the blanks all reappear...
i agree
This is really useful but is it is possible to apply this to skip level hierarchies?
Hi , how are you , thanks for the video , I have a question , is that applicable if the blank is in an intermediate level , I can see that level 4 in the example is the last level
You SAVED my night!
🥳
Hi Ruth, This is what I was looking for. Thanks for the video. But When I try to export the visual output to excel, the excel is blank and has only column titles. No data exported. Can you help me fix this?
I have the problem wit h the decomposition tree, do you know, how can i remove the blanks from the decomposotion tree. I cant hide the blanks
So many good tips in every video.
Thanks!
Thank you, this was a big help. I had a switch() measure that had a format in it and i was having trouble figuring out why it was always non-blank... turns out it was when the format statement was used!
🤩
/Ruth
This doesn't seem to work in 2024 unfortunately.
Can you do an updated tutorial for Dax105 and this one? (112). It seems that PowerBI recognizes Null in the powerquery editor but doesn't recognize it outside of the editor?
That is pretty slick. Thanks for sharing.
Thanks Brad :)
For multiple levels, you can use:
R =
SWITCH (
TRUE (),
ISINSCOPE('Table'[Level 2]) && ISBLANK(VALUES('Table'[Level 2])), BLANK(),
ISINSCOPE('Table'[Level 3]) && ISBLANK(VALUES('Table'[Level 3])), BLANK(),
ISINSCOPE('Table'[Level 4]) && ISBLANK(VALUES('Table'[Level 4])), BLANK(),
"."
)
Fantastic! solve my problem, once more time! Thanks! You area amazing.
Amazing content found when needed
But I'm facing one issue when I'm adding values from another table like add sales from other table which have relationship with it blank space reappearing
Hello, how would you do this with multiple columns with blank values ?
Really great video - love the DAX's fridays
Thanks! And have a great weekend:)
/Ruth
Is there a download for #112? I checked the site, and it appears to be missing.
SWITCH(TRUE(),
AND(ISINSCOPE(hier[Level 2]),ISBLANK(VALUES(hier[Level 2]))),BLANK(),
AND(ISINSCOPE(hier[Level 3]),ISBLANK(VALUES(hier[Level 3]))),BLANK(),
AND(ISINSCOPE(hier[Level 4]),ISBLANK(VALUES(hier[Level 4]))),BLANK(),
1)
This scenario is not working for me, I have a last level which is always there, how to handle this, any help would be great!
I seem to have already seen this dataset :). Thank you for the demo on the isinscope() function.
Multipurpose dataset 😉
/Ruth
This is amazing!! Thank you so much. You have save me so much time
At your service !!
Awesome solution, thank for sharing
How do we do if we have blanks in more than one level? That's he problem I have
Excellent, that's what I need
Wonderful!
I like your gestures, You're so pretty :)
Thanks for the great&helpful video.
Also this method is not working when there is values like start date and finish date , blanks are still there
Thank you very much!
Pleasure is all mine :)
ruth muchas gracias , excelente solucion , siempre tan bien explicado.
Todo un placer ;)
/Ruth
Hi, Thanks a lot, Could you please let me know how show parent values on parent instead of aggregation of child values? I used your code to remove Blank and add Selectedvalue() function in Else part of Switch. It works but some parents have value others not. and when I filter the child, Its parent's value changes to child's value.
Hi Zara, I am currently also struggling with this, did you perhaps find a solution for this? Kind regards!
Imagine I have some Cost or Profit Colum in my original flatten table for each task? Is there a way to use this workaround and also display my profit or costs right next to the hierarchy for the corresponding node without having blanks?
The details are important, pleas post in the power Bi community and give as many details as possible.
/Ruth
Very helpful! But then, when you try to export the matrix to csv, the csv is empty. Why ?
Really? Didn’t know that. Can you ask the power bi team at the power bi community?
/Ruth
Very Helpful ! please cann you explain me haw i can use this with Power BI Connected directly with my azure Analysis Services ?. my data is in ssdt 2019. i didn't found the best way to remove the blank . because i could'nt use power Query Editor.
Ask the backend team to do that for you!
/Ruth
thx for your answer, please cann you explain it well for me . i am the one who have to do it. we dont have a Backend Team. do i have do to it in my SQL Server ?
Thanks! I've implemented this trick successfully. However, I need to display a text value instead of a numeric value, I need to be the value of the last level of the hierarchy. Can someone help me? I can't find a workaround. Thanks!
Dear Jose, I am currently also struggling with this problem. Did you perhaps find a solution for this in the meanwhile? Kind regards!
Hi, this is a great solution but have anyone maybe found some workaround or a solution to these (+) icons other than just removing them? I would really like to have them working properly in this situation.
Have you got any solution for this ?
nice trick
Thanks :)
/Ruth
fantastic
🎉
/Ruth
TOP!
I have dataset in which I would like to kill blanks in top levels of my hierarchy, perfectly to not display level of hierarchy which are having blanks. Is there a way to do this with Isinscope? Thank you!
Maybe this will help?
ua-cam.com/video/YEuxONjCl1A/v-deo.html
/Ruth
Not working please advise
Super! Thanks a lot for this method!
Thank you very much!!!
My pleasure
Thank you very much!
My pleasure !