Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct Count Across Tables

Hi there,

 

I've searched to no avail for the answer to my question. I have a table with a universe of transactions like the below with three columns.

 

Customer Transactions:

Sender:       Recipient:    Dollar $:

Bob             Alice             $4

Tom             Sally             $6

Jane             Barbara        $2

Alice             Sally            $7

Bob              Tom             $9

 

I then have a multi-column table of "VIP customers" that houses the VIPs name as well as some other profile information that looks like:

 

VIP Table:

VIP Name:         Owns Dog?       Owns Cat?

Bob                     Yes                     Yes

Barbara               Yes                     No

Chris                    No                     Yes

 

What I'd like to do is figure out the number of distinct times Bob, Barbara & Chris have made a transaction in "Transactions Table" (the other data is irrelevant, but included to show that there exist multiple columns in each table) to guage how many of my "VIPs" have been active. The right answer would be 2 since Chris made no transactions and I'm not looking for the amount of total transactions made by the VIPs, only if they actually made one. I would like the result of this to show in a Card Visualization (in the hypothetical described here it would show "2").

 

Conceptually, this makes sense as I'd like to perform a distinct count of how many times a name from "VIP table" appears in the "Transactions Table," but I can't for the life of me figure out the right combination of dax to get me there.

 

Any help would be much appreciated.

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 

    Please try

    Count =
COUNTROWS (
    INTERSECT (
        VALUES ( 'VIP Table'[VIP Name] ),
        DISTINCT (
            UNION (
                VALUES ( 'Customer Transactions'[Sender] ),
                VALUES ( 'Customer Transactions'[Recipient] )
            )
        )
    )
)

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous So Bob shows up twice and Barbara once which would be 3 or does it not count when they are a Recipient? If that is the case, it should just be a simple COUNTROWS(RELATED or COUNTROWS(RELATEDTABLE if there is a relationship based on VIP Name and Sender. Otherwise, you could do a MAX to get the VIP and then a COUNTROWS(FILTER(...

     

    Or are you saying Bob shows up in the transaction table and also Barbara? In that case, you could do something like:

    Measure = 
  VAR __VIPs = SELECTCOLUMNS('VIP Table', "__VIP", [VIP Name]
  VAR __Transactions = UNION(SELECTCOLUMNS('Customer Transactions', "__Customer",[Sender]), SELECTCOLUMNS('Customer Transactions', "__Customer",[Recipient]))
RETURN
  COUNTROWS(DISTINCT(FILTER(__Transactions, [__Customer] IN __VIPs)))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    First, grateful for the prompt and thoughtful response! 

    To clarify,

     

    First: I'm trying to see if any person from the "VIP table" shows up in the "Transactions Table" irrespective of which column they appear.

    Second: If they do appear, I only want to count them once (even if they appear multiple times). 

     

    Third: I'd like the sum of all those unique appearances. So in the case outlined, the sum would be 2 (since Barbara and Bob do appear at least once in the table). 

    Not at a computer to try your proposed solution, but wanted to offer some clarity in the event it was helpful. Thank you again!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Greg_Deckler so I can see where this formula is going and I think it will get me there. the only problem I'm facing when inputting is that after "RETURN" it won't autofill for this part:

      COUNTROWS(DISTINCT(FILTER(__Transactions, [__Customer] IN __VIPs)))

     

    Further, should there be a paran after this portion:

     

      VAR __VIPs = SELECTCOLUMNS('VIP Table', "__VIP", [VIP Name]

     

    Let me know your thoughts, but think I'm on the right path.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    Please try

    Count =
COUNTROWS (
    INTERSECT (
        VALUES ( 'VIP Table'[VIP Name] ),
        DISTINCT (
            UNION (
                VALUES ( 'Customer Transactions'[Sender] ),
                VALUES ( 'Customer Transactions'[Recipient] )
            )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1 I think this works! If you have a moment would you elaborate a little bit on the dax logic? I'm unfamiliar with INTERSECT and how it relates to the argument. Assuming UNION brings together the two Customer Transactions into one searchable range/table? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        tamerj1 also, if I wanted to add a condition that the DAX should count only if the "Dollar $:" is greater than some x number, would I simply revise to the following?

         

        Count =
CALCULATE(
COUNTROWS (
    INTERSECT (
        VALUES ( 'VIP Table'[VIP Name] ),
        DISTINCT (
            UNION (
                VALUES ( 'Customer Transactions'[Sender] ),
                VALUES ( 'Customer Transactions'[Recipient] )
            )
        )
    )
), DOLLAR $ > $3
)