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. ...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft 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

       

      • kamiscfr's avatar
        kamiscfr
        Regular Visitor
        9 years ago

        ...no.

         

        PartnerSales subtracts All parter sales but only partner sales within the filter context are allowed to be subtracted. I need both conditions simultaneously. :smileyfrustrated:

  • 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