cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DouweMeer Responsive Resident
Responsive Resident

Remove filters from other table

I’m trying to create something like:

Countrows ( distinct ( selectcolumns ( allexcept ( ‘table 1’ , ‘table 1’[column A] , ‘table 2’[column A] ) , “test” , ‘table 1’[id] ) ) )

But allexcept doesn’t allow me to use a column from a second table in a measure. How do I make this work? The measure is intended for a matrix containing both internal and external filters. Only a part of the internal and all of external filters should remain.

9 REPLIES 9
Super User IV
Super User IV

Re: Remove filters from other table

@DouweMeer it is not clear what you are trying to do. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Community Support
Community Support

Re: Remove filters from other table

Hi @DouweMeer 

 

You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
DouweMeer Responsive Resident
Responsive Resident

Re: Remove filters from other table

@v-diye-msft 

Due to my companies policy, I'm unable to share any data. If you know a trick where I can upload/ create an excel and share it here, let me know. But things like Box, OneDrive, DropBox and similar are block by IT policy. 

I was hoping that the DAX expression would suggest enough. The problem is that in a measure allselected and allexcept are filter functions that only work in its own table. So if you want filters removed/ maintained from a secondary table, you can't. I'm looking for a way past it. For now my only idea is to use allselected on the required table and reapply the internal filters. How I will apply filters from a second table on a DAX table reference, I don't know yet. 

Community Support
Community Support

Re: Remove filters from other table

Hi @DouweMeer 

 

You can copy the simple table and paste it here. list the table1,table2,and the expected results. we'd like to provide further help.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
DouweMeer Responsive Resident
Responsive Resident

Re: Remove filters from other table

@v-diye-msft 

 

Let me see....

 

Table1    
Case ProductTimeSourceType
1A3ExternalComplaint
2B5.5ExternalComplaint
3A2.7ExternalComplaint
4A1.5InternalAnalytics
5B

6

External

Complaint

 

Quite fancy

 

Table2  
ProductTypePrototype
AElectricYes
BGasNo
CElectricNo
DGasYes

 

Relationship between table 1 and 2 is via the Product field.

 

So I get a matrix as follows:

 

  Yes No 
  ElectricGasElectricGas
ExternalComplaint    
 Analytics    
InternalComplaint    
 Analytics    

 

And for each square in the matrix I need 2 calculations.

Calculation 1 : takes a distinct count of cases, the context is all cases but with the filters External and Internal maintained plus all external filters (Teams, time period, etc)

Calculation 2 : the other one takes a distinct count of products from table 1 or 2 (by relationship it shouldn't matter). The distinct count product is in the context it is put into by the matrix itself.  

(eventually I'm curious to the ratio)

Community Support
Community Support

Re: Remove filters from other table

Hi @DouweMeer 

 

Sorry I still don't quite understand your requirement, based on your info, would you like to get below results?

 

8.PNG

Pbix attched. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
DouweMeer Responsive Resident
Responsive Resident

Re: Remove filters from other table

Hello @v-diye-msft 

Thank you for your attempt :). Comes close.

 

  Yes   No    Total 
  Electric Gas Electric Gas    
  Calc1Calc2Calc1Calc2Calc1Calc2Calc1Calc2 Calc1Calc2
ExternalComplaint414 4 41 42
 Analytics         00
InternalComplaint        00
 Analytics111 1 1  11

 

This would be my table. Calc1 shows the same values for each column as the context from the columns should be excluded. Calc2 does include them. So I would consider Calc2 not to be the difficult one, but Calc1 is. Allselected doesn't work as the filters from the columns come from the product table rather than the case table, but distinctcount ( 'Table1'[Case] ).

 

As far as I understand is that you can't manipulate the context by DAX of a relationable table while having another as the base for your calculations.

Community Support
Community Support

Re: Remove filters from other table

Hi @DouweMeer 

 

What about this one?

02.PNG

Pbix attached.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
DouweMeer Responsive Resident
Responsive Resident

Re: Remove filters from other table

@v-diye-msft 

This is more a 'cheat way' that works in this example. You're removing all filters from everywhere. However, in my situation, there are also explicit filters in the second table as well as a third table. With Allexcept you would also remove those filters. 

 

Getting back to my original one:

 

D ( A ( ( C ) , B ) )

 

Part A

The table reference

selectcolumns ( ... )

 

Part B

The field calculated (distinctcount cases)

... ) , “test” , ‘table 1’[id] )

 

Part C

The Context

(...) allexcept ( ‘table 1’ , ‘table 1’[column A] , ‘table 2’[column A] )

Note C1: 'Table1'[Column A] would refer to the implicit filter (External/ Internal). 

Note C2: 'Table2'[Column A] would refer to an explicit filter in the second table. This could also be a third table.

 

Part D

Final calculation

Countrows ( distinct ( ... ) ) 

 

So far I'm struggling at the context (Part C) as Allexcept (...) won't allow to have a column from a second table to be included in the allexcept funciton. My second thought was to remove implicit filter and reapply them by a combination of selectedvalue and allselect. However, if one of the selectedvalues is from a second table, I'm not aware of a DAX function that can apply a value in a second related table.

 

Perhaps a combination of allselected, selectedvalue and userelationship?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors