Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago
Solved

Different Customers

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 ...
Need Help
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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).

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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.