Power BI DISTINCTCOUNT DAX function is used to counts the number of distinct values in a column.
This functions comes under Aggregation functions Dax categories.
Refer similar DAX : COUNTROWS , COUNTBLANK , COUNT, COUNTA & COUNTX
Syntax:
DISTINCTCOUNT(<column>)
Description:
S no. | Parameter | Description |
1 | column | The column that contains the values to be counted |
Dataset format as below:
ID | Amount | Blank | Boolean | SalesDate | Name |
1 | TRUE | 29/5/2020 | A | ||
2 | 1000 | FALSE | B | ||
3 | 2000 | TRUE | 29/5/2020 | ||
4 | 1000 | FALSE | 28/5/2020 | C |
Note:
- The only argument allowed to this function is a column.
- DISTINCTCOUNT function includes the BLANK value.
- When the function finds no rows to count, it returns a BLANK, otherwise it returns the count of distinct values.
Step 1: Now we will count Distinct number of values under “Amount” Column. So for this create one new measure.
DISTINCT COUNT = DISTINCTCOUNT(SampleTable[Amount])
Output= 3
Step 2: Now DISTINCTCOUNT DAX function with other columns.
With Blank column:- DISTINCT COUNT = DISTINCTCOUNT(SampleTable[Blank])-- Output = 1 With ID column:- DISTINCT COUNT = DISTINCTCOUNT(SampleTable[ID])--- Output = 4 With SalesDate column: DISTINCT COUNT = DISTINCTCOUNT(SampleTable[SalesDate])---- Output = 3
You can also used DISTINCTCOUNT DAX with FILTER Function:
DistinctCountWithFilter = CALCULATE( DISTINCTCOUNT( SampleTable[Amount] ) ,Filter(SampleTable, SampleTable[ID] IN {1,2,4} ) ) Output = 2
Refer similar DAX : COUNTROWS , COUNTBLANK , COUNT, COUNTA & COUNTX
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.