Forum Discussion
Impactful IndividualRemove filters from other table
Impactful Individual
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)
Community SupportHi 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
- DouweMeer
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?