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], "" )
            )
        )
    )
)

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