Find match and count across tables RRS feed

  • Question

  • I have multiple fact tables and trying to extract count of an instance of items that is in one table but  not in the other.

    i am really stuck in this. Please see Sample Data as below:

    Please assist. Thank you.

    Old Table      New Table                       expected result 

    ab                  ab                  Items count in both table (New and  Old) :  2   (ab & af)

    ac                  ad                   Items count in new that are NOT  in Old:     (ad, ak & aj)

    af                   ak                   Items count in Old that are NOT  in New:   3    (ac, ag & kl)

    ag                  aj

    kl                   af

    Monday, June 17, 2019 9:07 AM

All replies

  • Hi,

    You may use such code:

        old = Excel.CurrentWorkbook(){[Name="OldTable"]}[Content][Column],
        new = Excel.CurrentWorkbook(){[Name="NewTable"]}[Content][Column],
        final = [both = List.Count(List.Intersect({new, old})),
                onlyNew = List.Count(List.Difference(new, old)),
                onlyOld = List.Count(List.Difference(old, new))]
    Monday, June 17, 2019 9:25 AM
  • Hi,

    thanks for quick response. 

    Not sure how to use this code  for DAx Power Pivot.

    Monday, June 17, 2019 9:53 AM
  • It's Power Query (M) code. If you need DAX, you may try this measures:

    Items count in both table (New and  Old):=COUNTROWS(INTERSECT('NewTable', 'OldTable'))
    Items count in new that are NOT  in Old:=COUNTROWS(EXCEPT('NewTable', 'OldTable'))
    Items count in Old that are NOT  in New:=COUNTROWS(EXCEPT('OldTable', 'NewTable'))

    Monday, June 17, 2019 10:26 AM