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

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

     

3 Replies

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

     

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