Overwrite relationship filter to get other Sales

[dnl_8]

Hi,

I am wondering how can I do the following:

I have a table:
Card

CardID	CompanyID
Card1	Company1
Card2	Company2

Sales

Card ID	Customer ID	Sales
Card1	Customer1	10
NoCard	Customer1	5
Card1	Customer2	20
NoCard	Customer2	10

I would like to be able to calculate the following:

I select Company1 from the Card table, and I want to be able to say that Customer1 and Customer2 ( who used Card1 which belongs to Company1) had 5 and 10 sales with NoCard ID.

So generally, I would like to get the Customer's NoCard sales, and link it to the companny whose card they used for Card1-Customer1-10 and Card1-Customer2-20 Sales rows.

So the wished outcome would be
Company1 - NoCard- 15Sales

I am able to join the 2 tables on CardID, but generally that only filters the Card transactions, where there is NoCard, it does not calculate.

Thank you!

[DOLEARY85]

Hi,

i think what you need to do is create a link table, use power query to duplicate/reference the sales table and remove all the no card rows and the sales column as below:

then set your model to have an inactive relationship from this link table to the sales table on customer id,

you should then be able to use this measure:

Measure 5 = calculate(sum(Sales[Sales]),USERELATIONSHIP('Sales Link'[Customer ID],Sales[Customer ID]))

the table will have the company ID from the card table, card id from the sales table and the measure which i put in the sales table.

hope this helps

[Greg_Deckler]

@dnl_8 You need to use ALL to bring all of the rows into context and then filter for NoCard. Like this:

No Card = 
    VAR __Customers = DISTINCT('Table2'[Customer ID])
    VAR __Table = FILTER(ALL('Table2'), Table2[Customer ID] IN __Customers && 'Table2'[Card ID] = "NoCard")
    VAR __Result = SUMX(__Table, [Sales])
RETURN
    __Result