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 Everyone
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?
Data Structure
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
Matrix Structure
Table C items added as rows
Table B items added as columns
Calculated measure from the Fact Table as value. Blanks shown as 0
Current Behaviour
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.
Desired Behaviour
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.
@v-janeyg-msft I want to show blanks as zeroes - just not the items I filter out. What I will probably end up doing is adding a AName column to table B.
Thanks for your help!!
"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.
Hi, @EP1
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.
Like this:
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.
Best Regards,
Community Support Team _ Janey
@v-janeyg-msft I want to show blanks as zeroes - just not the items I filter out. What I will probably end up doing is adding a AName column to table B.
Thanks for your help!!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |