Filter A Second Column by Filter Context

[kamiscfr]

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.

[Eric_Zhang]

@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]

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

[Anonymous]

Here a link to the solution.

https://stackoverflow.com/questions/48565960/filter-and-sum-a-second-colunmn-by-filter-context

[kamiscfr]

Dear Eric,

thanks so much for helping.

I tried to understand the formula (for hours ) 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]

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

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

Best

Frank

[kamiscfr]

...no.

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