If you spend enough time working with SSAS Tabular or Power Pivot data models, you are probably familiar with dealing with duplicate key values in lookup columns. Duplicate key values are not permitted in Tabular models and you will not be able to create a relationship when duplicates are present. If you try to do so you will encounter an error like this:
When working with large lookup tables, you need a DAX expression to find these duplicates in order to fix them. Thankfully, Teo Lachev has shared a very concise and elegant formula for identifying and counting duplicate key values.
=CALCULATE (COUNTROWS(), ALLEXCEPT(Table1, Table1[ID]))