DAX formula to compute the distinct number of accounts that have duplicate amounts

[CheikhnaML]

Hello everyone,

I am new to the forum and I would be glad to anyone who could help me resolve the next issue :

The next image is a screenshot of my power bi report.

It is a table that contains :

• First column “account number & name” (taken from my table of facts called "FEC")
  Second column is a list of amounts also taken from the same table (not summarized)
• third column is the count of the amounts in my facts table (in order to identify duplicates) that I calculated by using this DAX formula : COUNT(FEC[Montant])

This table is done as follows :

Now, I would like to compute the number of distinct accounts that have duplicates (ie counts >=2).

I tried the next formula :

#Distinct accounts =
CALCULATE(

DISTINCTCOUNT(FEC[CompteNum]),

    FILTER(

    FEC,

    [#Occurences]) >= "2"

    )

)

but I am running into an error that basically says that 

In case, you would need my data model : here it is :

Sorry for the fact that my power bi interface is in French

Thank you in advance

[thedatahiker · Accepted solution]

@CheikhnaML  You can try something like this. I added in the Montant as I assumed you only wanted to include accounts where they had the duplicate of the same Montant values. 

CALCULATE (
DISTINCTCOUNT ( FEC[CompteNum] ),
FILTER (
ADDCOLUMNS (
SUMMARIZE (
'FEC',
'FEC'[CompteNum],
'FEC'[Montant] ),
"#Occurences", [#Occurences] )
[#Occurences] >= 2))

[thedatahiker · Accepted solution]

Hi @CheikhnaML 

Happy to try and explain.

This is a simplistic example of what is happening in the DAX query I shared with you

1. With Summarize we are creating a temporary table that only includes the unique combination of account id and montant from the SEC table.
2. Using the temporary table in the step above we are using the function ADDCOLUMN to add a new column for the # occurrences.
   1. You can think of this similar to using a group by query in SQL 
3. Now that we have that table constructed in memory we can filter the rows of that table for only the values that have #Occurences >= 2
   
   1. You can think of this as similar to a having clause in SQL
4. We now use the previously returned table as the basis for our CountDistinct of account id
   
   In your original query you were applying the filter on a row context. Essentially you are saying give me rows of this table where #occurences >= 2. Since every row in your table can only have #occurences = 1 you don't get any results.

This is my best attempt at explaining the logic. It would likely be helpful to review documentation from SQLBI about this topic. Best Practices Using SUMMARIZE and ADDCOLUMNS - SQLBI

[CheikhnaML]

@thedatahiker Thank you for taking the time to answer my question. 

I appreciate it. 

[thedatahiker · Accepted solution]

@CheikhnaML  You can try something like this. I added in the Montant as I assumed you only wanted to include accounts where they had the duplicate of the same Montant values. 

CALCULATE (
DISTINCTCOUNT ( FEC[CompteNum] ),
FILTER (
ADDCOLUMNS (
SUMMARIZE (
'FEC',
'FEC'[CompteNum],
'FEC'[Montant] ),
"#Occurences", [#Occurences] )
[#Occurences] >= 2))

[CheikhnaML]

Hi @thedatahiker,

Thank you for your reply. 

It worked. 

Would you mind tell me why my formula was not working ?

Why was I obliged to used the "Summarize" fonction inside my "Filter" function in order to make it work ?

Thank you in advance.