How to find Distinct count

DAX DAX Calculations
1

Hi All,

I would like to know the distinctcount of applications which are at In Build and Maintain.
For example i have .CA is the Application Name and it is coming 2 times but the status shows this is at Maintain since there is a Number associated 2 times when i count for my calculation i should still count .CA as 1 application. Similarly A&H (Application name) coming multiple times but it as at Maintain, it should count as 1 application.
In general , i need to know count(Distinct) of Applications at Maintain and Build with out removing duplicates(application names) as there is a number (Column) associated to it which is different.

Below is the table for your reference

Application Names Status Number
.CA Maintain BSN0009375
.CA Maintain BSN0012682
.Ge Maintain BSN0009376
.Op Maintain BSN0009379
'@C Maintain BSN0009381
'@C In Build BSN0013221
'@C In Build BSN0009815
'@C Maintain BSN0009383
'@C Maintain BSN0009385
'@R Deleted BSN0009822
'@v Maintain BSN0001258
'@W Maintain BSN0001259
104 Maintain BSN0009824
1C In Build BSN0013666
1C: Maintain BSN0009388
2FA Maintain BSN0013140
4D Maintain BSN0009391
7E Maintain BSN0001257
90K Maintain BSN0010089
A&H Maintain BSN0012691
A&H Maintain BSN0009397
A&H Maintain BSN0009399
A&H Maintain BSN0008579
A&H Maintain BSN0008580
A&H Maintain BSN0008581
A&H Maintain BSN0009830
A&H Maintain BSN0009402
AAS Decommissioned BSN0009404
ABA Maintain BSN0008966
ABA Maintain BSN0008582
ABA Maintain BSN0008967
ABA Maintain BSN0008968
ABA Maintain BSN0009837
ABA Maintain BSN0009406
ABA In Build BSN0013138
ABA In Build BSN0013477
ABA Maintain BSN0008969
ABA Maintain BSN0009410
ABA Maintain BSN0009413
ABA Maintain BSN0009414
ABA Maintain BSN0008970
ABA Maintain BSN0008583
ABA Maintain BSN0010045
ABA Maintain BSN0009486
ABA Maintain BSN0008971
ABA Maintain BSN0009418
ABA Maintain BSN0009420
ABA Maintain BSN0009423
ABA Maintain BSN0008584
ABA Maintain BSN0008972
ABA Maintain BSN0008973
2

Hi @Nagi_k,

Try creating 2 measures something like this:

In Build Count =
CALCULATE ( COUNTROWS ( ā€˜Applicationā€™ ), ā€˜Applicationā€™[Status] = ā€œIn buildā€ )

Maintain Count =
CALCULATE(COUNTROWS(ā€˜Applicationā€™),ā€˜Applicationā€™[Status]=ā€œmaintainā€)

Regards,
Hemant

3

Hi @Hemantsingh,

But how do i get a distinct count of applications which are In Build and maintain

4

Hi,

Can you elaborate a bit more as what you would like to see as end result in excel?

Regards

5

Hi @Hemantsingh,

I would like to know the distinctcount of applications which are at In Build and Maintain.
For example i have .CA is the Application Name and it is coming 2 times but the status shows this is at Maintain since there is a Number associated 2 times when i count for my calculation i should still count .CA as 1 application. Similarly A&H (Application name) coming multiple times but it as at Maintain, it should count as 1 application.
In general , i need to know count(Distinct) of Applications at Maintain and Build with out removing duplicates(application names) as there is a number (Column) associated to it which is different.

Hope i have explained properly this timeā€¦Below is the table for your reference

Application Names Status Number
.CA Maintain BSN0009375
.CA Maintain BSN0012682
.Ge Maintain BSN0009376
.Op Maintain BSN0009379
'@C Maintain BSN0009381
'@C In Build BSN0013221
'@C In Build BSN0009815
'@C Maintain BSN0009383
'@C Maintain BSN0009385
'@R Deleted BSN0009822
'@v Maintain BSN0001258
'@W Maintain BSN0001259
104 Maintain BSN0009824
1C In Build BSN0013666
1C: Maintain BSN0009388
2FA Maintain BSN0013140
4D Maintain BSN0009391
7E Maintain BSN0001257
90K Maintain BSN0010089
A&H Maintain BSN0012691
A&H Maintain BSN0009397
A&H Maintain BSN0009399
A&H Maintain BSN0008579
A&H Maintain BSN0008580
A&H Maintain BSN0008581
A&H Maintain BSN0009830
A&H Maintain BSN0009402
AAS Decommissioned BSN0009404
ABA Maintain BSN0008966
ABA Maintain BSN0008582
ABA Maintain BSN0008967
ABA Maintain BSN0008968
ABA Maintain BSN0009837
ABA Maintain BSN0009406
ABA In Build BSN0013138
ABA In Build BSN0013477
ABA Maintain BSN0008969
ABA Maintain BSN0009410
ABA Maintain BSN0009413
ABA Maintain BSN0009414
ABA Maintain BSN0008970
ABA Maintain BSN0008583
ABA Maintain BSN0010045
ABA Maintain BSN0009486
ABA Maintain BSN0008971
ABA Maintain BSN0009418
ABA Maintain BSN0009420
ABA Maintain BSN0009423
ABA Maintain BSN0008584
ABA Maintain BSN0008972
ABA Maintain BSN0008973
6

Hello @Nagi_k,

Would you be able to share .pbix file of your data?

Regards
Kumail Raza

7

For distinct count.pbix (26.1 KB)

Here we go

8

Hello @Nagi_k

We can use DISTINCTCOUNT() function to get unique application name and then use it with status column.

image
image1870Ɨ851 82.7 KB

The pbix file is attached for reference.

I hope this helps.

Regards
Kumail RazaFor distinct count Updated.pbix (27.0 KB)

9

@Kumail , Thank you
But i want to show in a Card separately by writing a measure

like Total No. of applications -
Total no. of Applications at Build stage
Total no. of Applications at Maintain

1 Like
10

@Nagi_k

image
image1851Ɨ783 75 KB

The pbix is attached for your reference.

For distinct count Updated.pbix (27.9 KB)

11

@Nagi_k

I hope the above solution helps.

Regards
Kumail Raza

12

@Kumail Thank you so much ā€¦