Forum Discussion

dnl_8's avatar
dnl_8
New Member
3 years ago

Overwrite relationship filter to get other Sales

Hi,

I am wondering how can I do the following:

I have a table:
Card

CardIDCompanyID
Card1Company1
Card2

Company2

 

Sales

Card IDCustomer IDSales
Card1Customer110
NoCardCustomer15
Card1Customer220
NoCardCustomer210

 

 

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!

Need Help
Tips and Tricks

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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
  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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