How to use DAX to return a count of 1 only once with duplicates and 1 with non-duplicates?

[Tooba_kazmi]

Hi All, 

How to return a count of 1 in another column having duplicates and non-duplicates? Duplicates should only be counted once just as shown in the table below:

ID#	Count
ID1	1
ID2	1
ID1
ID3	1
ID1

[Tooba_kazmi]

Hi, the code does not return count of 1 with duplicates.  

[riyasiak]

Not sure if your question is well phrased, believe you are looking to count the unique values for column ID#, if so this can be achieved even without DAX, within the visual just select Count (Distinct).

[123abc]

To achieve this in Power BI using DAX, you can create a calculated column that calculates the count based on your conditions. You can use the following DAX formula to create such a calculated column:

Count Column =
VAR CurrentID = YourTable[ID#]
VAR DistinctCount = COUNTROWS(FILTER(YourTable, YourTable[ID#] = CurrentID))
RETURN
IF(DistinctCount > 1, BLANK(), 1)

Here's how the formula works:

1. It first stores the current ID in a variable called CurrentID.
2. It calculates the distinct count of rows in the table where the ID matches the CurrentID using the FILTER and COUNTROWS functions. This gives you the number of occurrences of the current ID in the table.
3. It uses an IF statement to check if the distinct count is greater than 1. If it is, it returns a blank value (count of 1 with duplicates). If it's not, it returns 1 (count of 1 without duplicates).

You should replace YourTable with the name of your actual table and ID# with the name of your actual ID column.

Once you create this calculated column, it will give you the desired count of 1 for both duplicates (counted only once) and non-duplicates in a separate column.