Can a slicer filter only rows on a matrix chart and not the columns?

[dspbi]

I have a matrix chart that shows all accounts with sales as Row items, then the product type in the column data, and the value of sale in the values section.

This works great unfiltered. It shows values where sales have been made on product type and no value in the product type that have no sales. 

I want to now add a slicer for account, to allow me to narrow down what users can see in the report as they please. The issue I have is when adding the same account field to a slicer, this not only filters on the account in the row data, but filters out any "product type" in the column data that has no value of sale in the value data. 

I want to be able to see the product type even if no sales are against them but these are automatically filtered out of the report and it shows only the product types with sales against them.

Do I need to establish different joins to acheive this or is there someway I can acheive this through a measure or calcualted column?

[Álvaro_SM]

Hi @dspbi , did you come to a solution in the end? I've got a similar problem and wanted to know if there is an easier way to ahieve it. Thanks!

[v-kelly-msft · Accepted solution]

Hi @dspbi ,

First create a dim table as below:

Slicer table = CROSSJOIN(VALUES('Table'[account]),VALUES('Table'[Type]))

Then create a measure as below:

Measure = IF(NOT(ISFILTERED('Slicer table'[account])),CALCULATE(SUM('Table'[sales]),FILTER('Table','Table'[Type]=SELECTEDVALUE('Slicer table'[Type]))),
IF(MAX('Table'[account]) in FILTERS('Slicer table'[account]),CALCULATE(SUM('Table'[sales]),FILTER('Table','Table'[Type]=SELECTEDVALUE('Slicer table'[Type])&&'Table'[account]=SELECTEDVALUE('Slicer table'[account])))+0,BLANK()))

And you will see:

before filter:

After filter:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[dspbi]

Hi ignore me for now I figured it out.

I will try the rest and come back to you - thanks again 🙂

[v-kelly-msft]

Hi  @dspbi,

Dim table is short for dimension table,waiting for your good news.🙂

Check my sample .pbix file for reference,if you still feel frustrated,welcome to be back to me.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[dspbi]

Hi Kelly

Sorry - I am not sure what you mean by create a dim table? Are there any guides on this as you have lost me at the first step I am afraid?

I have used dimension tables before but I am not sure how to create one in the manner that you have above?

[mahoneypat]

You can try just adding +0 to your Sales measure.

Regards,

Pat

[dspbi]

Thanks for the response. Unfortunatly this only attributes 0 values to values with data that already exists. IE, if the row has sales, it is adding zero, but where no data exists for a record it doesn't add the zero!

Thank you anyway.