Different Customers

[manideep547]

Hi all,

I have 4 different tables having the same customers and Id for the  all the 4 tables I need a count of the Ids who belong only to one table 
Example ID 1 having Transaction only on table A but not in Table B, Table C, Table D
Table A                                     Table B                               Table C                            Table D
ID                                                ID                                  ID                                             ID      
111                                            111                                 222                                           333
222                                           333                                  444                                            555
Count=2
111,222,333 Id has a transaction in two tables so we can ignore 
444,555 Id having Transaction only in one table then we have to count those IDS 

[Greg_Deckler]

I think that what @amitchandak suggested will work although I did not try its implementation. I went about this a little differently which I believe is more computationally efficient as it avoids measures and table scans, etc. Basically, you need a customer dimension table. Assuming that your actual data is more complex than presented, I did that this way:

Table = 
    DISTINCT(
        UNION(
            SELECTCOLUMNS('TableA',"ID",'TableA'[ID]),
            SELECTCOLUMNS('TableB',"ID",'TableB'[ID]),
            SELECTCOLUMNS('TableC',"ID",'TableC'[ID]),
            SELECTCOLUMNS('TableD',"ID",'TableD'[ID])
        )
    )

Now, create relationships based on ID between this table and your other tables. Then just create this column in Table:

Column = 
    VAR __1 = IF('Table'[ID] IN RELATEDTABLE(TableA),1,0)
    VAR __2 = IF('Table'[ID] IN RELATEDTABLE(TableB),1,0)
    VAR __3 = IF('Table'[ID] IN RELATEDTABLE(TableC),1,0)
    VAR __4 = IF('Table'[ID] IN RELATEDTABLE(TableD),1,0)
RETURN
    IF(__1 + __2 + __3 + __4 = 1,1,0)

All you need to do now is use Column in any visual with an aggregation of Sum. This method will be far more efficient given large dataset sizes. PBIX is attached.

[JarroVGIT · Accepted solution]

Hi @manideep547 ,

SingleIDs = 
VAR _unionOfAllTables = UNION(TableA, TableB, TableC, TableD)
VAR _unionWithOccurenceColumn = ADDCOLUMNS(_unionOfAllTables, 
                                "occurences", 
                                VAR _curID = [ID]
                                RETURN
                                COUNTROWS(FILTER(_unionOfAllTables, [ID] = _curID)))
RETURN
COUNTROWS(FILTER(_unionWithOccurenceColumn, [occurences] = 1))

This measure works, see attached PBIX. (please ignore Table1 and Table2, those were for other question).

[amitchandak]

Create a common customer dimension

customer =distinct(union(all(tableA[ID]),all(tableB[ID]),all(tableC[ID]),all(tableD[ID])))

Create a combined measure

measure = count(tableA[ID]) + count (tableB[ID]) + count (tableC[ID]) + count (tableD[ID])

Create 1 transaction measure

Gt 1 = sumx(filter(summarize(customer, customer[ID],"_sum",[measure]),[_sum]<=1),[_sum])