I have a matrix that I am struggling to filter because I want to show blanks as zeroes in the matrix.
I have two levels of master data and one fact table. If I filter by the lowest level of master data, the column items filter correctly but if I filter by the higher level of master data, instead of filtering out column items, the values show as zero.
I would appreciate it if someon could assist me - is there a way to show blanks as zeroes but filter column items by a higher level of master data?
3 Master Data tables (A, B and C) and 1 Fact Table
Table A links to Table B by means of a foreign key. The relationship is bi-directional.
Table B links to the Fact Table by means of a foreign key. Table B filters the Fact table
Table C links to the Fact Table by means of a foreign key. Table C filters the Fact table
Table C items added as rows
Table B items added as columns
Calculated measure from the Fact Table as value. Blanks shown as 0
If I filter by Table B, only the filtered items show in the matrix
If I filter by Table A, all Table B items show but the items not in the filter criteria show all values as 0
Note: this issue only occurs if the Table B items are in the columns. If Table B items are added as rows, instead of columns, the matrix behaves as desired.
If I filter by Table A, I want only the relevant items in Table B to show in the Matrix, like would be the case if I filtered Table B items or as would be the case if Table B items were added as rows instead of columns.
Sorry for the vagueness - I would be happy to clarify any point.
Solved! Go to Solution.
"I want to show blanks as zeroes in the matrix"
You can't do that. There is no content for the filter context that evaluates to Blank. If you want to show zeros then these zeros need to be in your data.
@lbendlin I am showing blanks as zeroes by changing blanks to zeroes in the measure calculation. However, my problem is that the second level master data filter works as desired when the items are added as rows, but not when added as columns.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.
According to your needs, I think you need to use blank() instead of 0. Otherwise, 0 will be filled with data that does not exist.
gap = IF(SUM(FactTable[Value])>0,1)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Community Support Team _ Janey
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!