The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Hi, the code does not return count of 1 with duplicates.
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).
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:
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.
User | Count |
---|---|
80 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |