Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
6 years ago

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 ...
Need Help
DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
6 years ago

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.

  • DouweMeer's avatar
    DouweMeer
    Icon for Impactful Individual rankImpactful Individual
    6 years ago

    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?