Forum Discussion

kamiscfr's avatar
kamiscfr
Regular Visitor
9 years ago

Filter A Second Column by Filter Context

Hi all,

 

I have a table with a Company ID, Date and Sales. If the customer is a group partner company, a further Partner Company ID column is filled with the Company ID of the internal customer.

 

I’d like to sum the sales but only for sales other than internal sales between the companies within the filter context. That is the sales with no (empty) Partner Company IDs or with Partner Company IDs other than the Company ID’s within the filter context.

 

I tried some formulas with “Values”, “Filter” and “Earlier” but didn’t make it.

 

Thanks a lot for helping.

 

Need Help

6 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    kamiscfr

    You can try a measure as below. See more details in the attached pbix file.

    external SumTotal =
CALCULATE (
    SUM ( yourTable[Sales] ),
    FILTER (
        yourTable,
        NOT (
            CONTAINS (
                CALCULATETABLE ( VALUES ( yourTable[Company ID] ), ALLSELECTED ( yourTable ) ),
                yourTable[Company ID], LASTNONBLANK ( yourTable[Partner Company ID], "" )
            )
        )
    )
)

    • kamiscfr's avatar
      kamiscfr
      Regular Visitor
      9 years ago

      Dear Eric,

       

      thanks so much for helping.

       

      I tried to understand the formula (for hours :smileyhappy:) but still I have some problems understanding:

       

        CALUCATE SUM - understand

        FILTER - understand

        NOT CONTAINS - understand

        CALCULATETABLE - Only the companies within the external filter context?

        LASTNONBLANK - Don't understand

       

      The sum seems to be ok, but I can not use it in a table, as the sceenshots shows.

       

      Thanks again

      Frank

       

       

      • kamiscfr's avatar
        kamiscfr
        Regular Visitor
        9 years ago

        Dear Eric,

         

        I think I found a solution when learning more about Dax by reading "The Definitive Guide to Dax". Here I learned about the funktion USERELATIONSSHIP.

         

        I created a new table "Companies" for the definition of the companies. Then I linked that table to the fact table twice. First with the Company ID and second with the Partner Company ID. The I use the first connection to sum the sales and the second connection with the USERELATIONSHIP function to sum the partner sales. Then I calculate sum of sales minus partner sales = correct sales.

         

        It seems to work correctly (and faster). And I understand it :smileylol:

         

        I'll test it with my real case. If I still have problems, I'll write again.

         

        Best

        Frank

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Hello Eric,

      I have similar problems with the DAX formula - the sum seems to be right, but the values per line are wrong.
      "IsExternal" does not work properly for me too.
       
       
      IsExternal = IF(CONTAINS(CALCULATETABLE(VALUES(Sales[CompanyID]);ALLSELECTED(Sales));
      	Sales[CompanyID];LASTNONBLANK(Sales[PartnerCompanyID];""));0;1)
      TotalSales = CALCULATE (
   SUM ( Sales[Sales] );
    FILTER (
        Sales;
        NOT (
            CONTAINS (
                CALCULATETABLE ( VALUES ( Sales[CompanyID] ); ALLSELECTED ( Sales ) );
               Sales[CompanyID]; LASTNONBLANK ( Sales[PartnerCompanyID]; "" )
            )
        )
    )
)

       

       

       

       

       

      The main problem is that the processing over 10+ million records takes forever and breaks off.

       

      PBIX File

       

      Thanks a lot.

      Best regards