cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dspbi
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1610962346371.png

After filter:

v-kelly-msft_1-1610962379125.png

 

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1610962346371.png

After filter:

v-kelly-msft_1-1610962379125.png

 

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

View solution in original post

Hi ignore me for now I figured it out.

 

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

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!

 

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
Super User IV
Super User IV

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


dspbi
Frequent Visitor

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.