Forum Discussion

rajul_rstgi's avatar
rajul_rstgi
Frequent Visitor
8 years ago
Solved

Counting distinct values

  I am working with a dataset which is a output of full joins across 3-4 systems. I am trying to get distinct count of the id's missing in each system   System A  System B  System C  AAA        A...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    rajul_rstgi

     

    Above will return the Count of Missing IDs.

    If you want a list of Missing IDs... Goto Modelling Tab and select NEW TABLE

     

    List of ID's in B missing in C =
EXCEPT (
    FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ),
    ALL ( TableName[SystemC_ID] )
)

     

stretcharm's avatar
stretcharm
Memorable Member
8 years ago

I would use the merge features of M (query editor) to get the data on a single row and then have an expression to count the missing values.

 

Load A B & C as 3 queries. Merge each system to each other on the Key with full outer and you should be able to get what you need.

 

Then use powerbi to display the counts and let you see the missing values. You can export data from a table or matrix which I find is helpful when I've found reconcillation issues.