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

DAX ignore row filter context, keep filters from slicer

Hello,

 

I have a visual with table that represents sales on Month-Customer-Product level. I need to create a measure that will display how many products were selected in Product slicer. The tricky part is that I need to show it in the table visual as well but the problem is that when I put my measure in the table, it is being evaulated at row level therefore it is limited to only one product. At first I tried with:

 
COUNTROWS ( VALUES ( 'DIM Product'[Material Name] ) )
 

But it always shows 1 for each row. Then I tried:

 

COUNTROWS ( ALLSELECTED ( 'DIM Product'[Material Name] ) )

 

and this time it is showing how many products for each customer and month are available in my Fact table. 

 

What I actually want it to show is 3 in every row, just like in card visual. So as I understand it correctly logic for this measure should be to ignore product filter from visual (row filter) and keep product filter from slicer. I can't use ALL function becasue it will erase selection from slicer as well. Is it possible in DAX to create such a measure?

 

PBIforum.png

Thanks in advance for any response!

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @xhurit ,

Sorry for replying late. Based on your description, you need to create a seprate slicer table like this:

Product slicer = DISTINCT('Table'[Product])

Then create your measure to count:

Count = COUNTROWS(ALLSELECTED('Product slicer'[Product]))

To interact with the source table, you can create the relationship between two tables based on the product column and set the table visual filter like this:

tb.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
lbendlin
Super User III
Super User III

Usually in such a scenario you use disconnected tables to break out of the filter context, and then apply the filters manually via the measure.

xhurit
Frequent Visitor

@lbendlin can you elaborate on this? Should I duplicate Product table? What measure should I use then? Which table will be used as a slicer then? 

v-yingjl
Community Support
Community Support

Hi @xhurit ,

Sorry for replying late. Based on your description, you need to create a seprate slicer table like this:

Product slicer = DISTINCT('Table'[Product])

Then create your measure to count:

Count = COUNTROWS(ALLSELECTED('Product slicer'[Product]))

To interact with the source table, you can create the relationship between two tables based on the product column and set the table visual filter like this:

tb.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Count = COUNTROWS(ALLSELECTED('Product slicer'[Product]))

can be reduced to 

Count = COUNTROWS('Product slicer')

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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.