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
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.
Solved! Go to 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.
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
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 :
£
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:
What i would like to achieve is that
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.
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
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:
(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 :
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
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.
@iLoveTea , in visual level filter check that level is not blank
User | Count |
---|---|
159 | |
110 | |
96 | |
85 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |