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
v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
6 years ago

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

    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's avatar
      v-diye-msft
      Icon for Community Support rankCommunity Support
      6 years ago

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

        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)