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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
iLoveTea
Frequent Visitor

Hide blank values in hierarchy matrix

Hello everyone,

I've been browsing past threads about hiding blank values in matrix with hierarchical data but i think i have a bug in doing so.

After i flag all blank lines, when i try to filter them out, all the values dissapear.
I have no idea why.
Here's an image showing the issue.

MicrosoftTeams-image (1).png

1 ACCEPTED SOLUTION

Hi , @iLoveTea 

According to your image, we can not make this measure return 1 and 0 and then filter it.

I test this method before,it dose not work the same as yours.

The method should return the value you need, you need to update this measure return teh value which this column need.And you need to replace all the column by the measures.

vyueyunzhmsft_0-1668648597668.png

That means you need to create two measures like that to replace this two yellow columns.You need to replace the 1 to your need value in the measure .

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

6 REPLIES 6
iLoveTea
Frequent Visitor

Hi @amitchandak @v-yueyunzh-msft 
I tried amitchandak but it didnt work
I tried yueyunzh but i dont have a value column to leverage.

here's what i have :

iLoveTea_0-1668603850981.png£
In field i have 9 categories.

The removeblank measure is : 

_Remove Blanks =

SWITCH(TRUE(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat2]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat2]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat3]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat3]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat4]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat4]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat5]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat5]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat6]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat6]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat7]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat7]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat8]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat8]))),BLANK(),

AND(ISINSCOPE('cache Activite_Hiera'[test_tact_code_cat9]),ISBLANK(VALUES('cache Activite_Hiera'[test_tact_code_cat9]))),BLANK(),

1)


When i filter the blanks in the filter panel, that happens:

iLoveTea_1-1668603928739.png

 

What i would like to achieve is that 

iLoveTea_2-1668604205910.png

 


Thank you for your help 

Hi , @iLoveTea 

According to your image, we can not make this measure return 1 and 0 and then filter it.

I test this method before,it dose not work the same as yours.

The method should return the value you need, you need to update this measure return teh value which this column need.And you need to replace all the column by the measures.

vyueyunzhmsft_0-1668648597668.png

That means you need to create two measures like that to replace this two yellow columns.You need to replace the 1 to your need value in the measure .

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

v-yueyunzh-msft
Community Support
Community Support

Hi , @iLoveTea 

According to your description, you want to "Hide blank values in hierarchy matrix". Right?

Here is a method you can refer to :
(1)This is my test data:

vyueyunzhmsft_0-1668586162269.png

(2)We can create a measure to replace the value in your visual:

Replace sum of value = IF( ISINSCOPE('Table'[Type1])&& SELECTEDVALUE('Table'[Type1])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type2])&& SELECTEDVALUE('Table'[Type2])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type3])&&SELECTEDVALUE('Table'[Type3])=BLANK(),BLANK(),IF(ISINSCOPE('Table'[Type4])&& SELECTEDVALUE('Table'[Type4])=BLANK(),BLANK(),SUM('Table'[Value])))))

(3)Then we can put the field on the visual :

vyueyunzhmsft_1-1668586529266.png

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

@iLoveTea 

This can help but you can't export all rows. Could you please check and let me know, how can i achieve this.

Thanks

Hey, let's move one step forward to this problem. I was getting the same problem and had it solved with this measure. But now, when I export the data to excel some of the rows are filtering out. No, visual level filter is applied. Even when I convert the matrix to a table visual the values are getting filtered out. Any help will be appreciated.

amitchandak
Super User
Super User

@iLoveTea , in visual level filter check that level is not blank

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.