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

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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