cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EP1
Advocate IV
Advocate IV

Matrix Filtering Column Items by Another Table

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.

1 ACCEPTED 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!!

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

"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.

@lbendlin please access the .pbix file here.

 

I have added two matrices and two slicers showing the current behaviour (filtering using table A) and desired behaviour (currently filtering using table B but want to filter using table A):

EP1_0-1636031100611.png

 

v-janeyg-msft
Community Support
Community Support

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)

 

 

vjaneygmsft_0-1636533063151.png

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!!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors