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

     

rajul_rstgi's avatar
rajul_rstgi
Frequent Visitor
8 years ago

Thanks. I already have the data merged in single row. I am trying to find a good way to create measure  to do a distinct count on each of these columns missing values, based on the distinct values in other 2 columns.

 

Hope this helps clarify the problem statement.

parry2k's avatar
parry2k
Super User
8 years ago

here is how i will do,

 

- unpviot your raw table so you have all two column one system and 2nd id

- remove blank rows

 

create another table by dupliating your raw table after above steps are applied, this new table,

 

- remove system column

- remove duplciate rows

 

so basically this new table will have only unique ids

 

close query editore and create relationship betwee id from raw to this new unique table

 

create two measures in your raw table:

 

AllId = new table

 

Total Id = Count(AllId[Id])

Another measure, 

 

Count = DISTINCTCOUNT(RawTable[Id])

Another measure,

 

Missing Ids = [Total Id] - [Count]

choose table visual, and drop following in table:

 

- System (Column from raw table)

- Missins Ids (calculated measure)

 

You will see missing id for each system, somethign like this