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

     

JarroVGIT's avatar
JarroVGIT
Resident Rockstar
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).