Remove filters from other table

[DouweMeer]

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.

[v-diye-msft]

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.

[DouweMeer]

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

[v-diye-msft]

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.

[DouweMeer]

@v-diye-msft 

Let me see....

Table1
Case	Product	Time	Source	Type
1	A	3	External	Complaint
2	B	5.5	External	Complaint
3	A	2.7	External	Complaint
4	A	1.5	Internal	Analytics
5	B	6	External	Complaint

Quite fancy

Table2
Product	Type	Prototype
A	Electric	Yes
B	Gas	No
C	Electric	No
D	Gas	Yes

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

So I get a matrix as follows:

		Yes		No
		Electric	Gas	Electric	Gas
External	Complaint
	Analytics
Internal	Complaint
	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)

[v-diye-msft]

Hi @DouweMeer 

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

Pbix attched. 

[DouweMeer]

Hello @v-diye-msft 

Thank you for your attempt :). Comes close.

		Yes				No					Total
		Electric		Gas		Electric		Gas
		Calc1	Calc2	Calc1	Calc2	Calc1	Calc2	Calc1	Calc2		Calc1	Calc2
External	Complaint	4	1	4		4		4	1		4	2
	Analytics										0	0
Internal	Complaint									0	0
	Analytics	1	1	1		1		1			1	1

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.

[v-diye-msft]

Hi @DouweMeer 

What about this one?

Pbix attached.

[DouweMeer]

@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?

[parry2k]

@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