Hi Team,I havae a dataset where i need to get distinct count of projectid where filter condition on cloumn Met/Not Met = "Met"However this dax is not working as i need unique projectid and i am getting entire count Kindly help to fix the issue Project IDMet/not met1289-03Not Met1289-04Not Met1790-01Not Met1898-01Not Met2455-01Not Met2966-01Not Met3319-01Not Met3890-01Not Met4690-01Not Met4721-01Not Met4783-03Not Met5385-01Not Met5571-01Not Met5710-01Not Met5850-01Not Met5952-01Not Met6193-01Not Met1287-05Not Met1293-12Not Met3494-02Not Met3881-02Not Met5450-01Not Met5929-01Not Met6008-01Not Met6211-01Not Met1244-04Not Met1258-10Not Met1293-09Not Met1921-01Not Met4320-01Not Met5800-01Not Met5864-01Not Met6031-08Not Met6259-01Not Met1258-11Not Met1869-01Not Met5450-06Not Met5713-03Not Met6548-01Not Met6826-01Not Met1250-08Not Met1258-06Not Met3101-01Not Met6618-01Not Met6620-01Not Met1267-06Not Met3319-02Not Met4534-01Not Met5472-01Not Met5626-01Not Met5712-01Not Met6546-01Not Met7030-01Not Met7311-01Not Met7469-01Not Met1290-05Not Met6748-01Not Met7472-01Not Met6985-01Not Met7326-01Not Met7380-01Not Met7561-01Not Met7696-01Not Met1869-04Not Met7748-05Not Met8080-01Not Met8524-01Not Met8535-01Not Met7250-05Not Met5772-07Not Met8262-01Not Met8539-01Not Met9211-02Not Met8751-01Not Met10162-01Not Met9757-04Not Met9778-01Not Met9757-01Not Met4768-01Not Met10352-04Not Met10890-01Not Met11365-01Not Met10823-01Not Met10934-01Not Met9542-04Not Met1200-01Met1203-01Met1293-07Met1898-02Met2034-12Met2047-01Met2049-01Met2065-01Met3347-01Met3550-03Met4219-01Met4442-03Met4658-01Met4960-01Met

Hi Anonymous ， Create a new table with the following dax expression. NewTable = SUMMARIZE ( 'Table', 'Table'[Project ID], "Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Met" ), "Not Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Not Met" ) ) Then create two new columns based on this. Grand Total = 'NewTable'[Met]+'NewTable'[Not Met]Condition = IF('NewTable'[Met]='NewTable'[Grand Total],1,0) Finally create a measure. count 1 = CALCULATE(COUNTROWS('NewTable'),FILTER('NewTable','NewTable'[Condition]=1)) specific value = VAR _condition_all = CALCULATE(COUNTROWS('NewTable')) RETURN [count 1]/_condition_all The result should be like this. Attach the pbix file for reference. Hope it helps. Best Regards,Community Support Team_Gao If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous

Not applicable

4 years ago

Solved

Distinct COuntx

Hi Team,

I havae a dataset where i need to get distinct count of projectid where filter condition on cloumn Met/Not Met = "Met"

However this dax is not working as i need unique projectid and i am getting entire count

Kindly help to fix the issue

Project ID	Met/not met
1289-03	Not Met
1289-04	Not Met
1790-01	Not Met
1898-01	Not Met
2455-01	Not Met
2966-01	Not Met
3319-01	Not Met
3890-01	Not Met
4690-01	Not Met
4721-01	Not Met
4783-03	Not Met
5385-01	Not Met
5571-01	Not Met
5710-01	Not Met
5850-01	Not Met
5952-01	Not Met
6193-01	Not Met
1287-05	Not Met
1293-12	Not Met
3494-02	Not Met
3881-02	Not Met
5450-01	Not Met
5929-01	Not Met
6008-01	Not Met
6211-01	Not Met
1244-04	Not Met
1258-10	Not Met
1293-09	Not Met
1921-01	Not Met
4320-01	Not Met
5800-01	Not Met
5864-01	Not Met
6031-08	Not Met
6259-01	Not Met
1258-11	Not Met
1869-01	Not Met
5450-06	Not Met
5713-03	Not Met
6548-01	Not Met
6826-01	Not Met
1250-08	Not Met
1258-06	Not Met
3101-01	Not Met
6618-01	Not Met
6620-01	Not Met
1267-06	Not Met
3319-02	Not Met
4534-01	Not Met
5472-01	Not Met
5626-01	Not Met
5712-01	Not Met
6546-01	Not Met
7030-01	Not Met
7311-01	Not Met
7469-01	Not Met
1290-05	Not Met
6748-01	Not Met
7472-01	Not Met
6985-01	Not Met
7326-01	Not Met
7380-01	Not Met
7561-01	Not Met
7696-01	Not Met
1869-04	Not Met
7748-05	Not Met
8080-01	Not Met
8524-01	Not Met
8535-01	Not Met
7250-05	Not Met
5772-07	Not Met
8262-01	Not Met
8539-01	Not Met
9211-02	Not Met
8751-01	Not Met
10162-01	Not Met
9757-04	Not Met
9778-01	Not Met
9757-01	Not Met
4768-01	Not Met
10352-04	Not Met
10890-01	Not Met
11365-01	Not Met
10823-01	Not Met
10934-01	Not Met
9542-04	Not Met
1200-01	Met
1203-01	Met
1293-07	Met
1898-02	Met
2034-12	Met
2047-01	Met
2049-01	Met
2065-01	Met
3347-01	Met
3550-03	Met
4219-01	Met
4442-03	Met
4658-01	Met
4960-01	Met

Anonymous
4 years ago
Hi Anonymous ，

Create a new table with the following dax expression.

NewTable = SUMMARIZE ( 'Table', 'Table'[Project ID], "Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Met" ), "Not Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Not Met" ) )

Then create two new columns based on this.

Grand Total = 'NewTable'[Met]+'NewTable'[Not Met]
Condition = IF('NewTable'[Met]='NewTable'[Grand Total],1,0)

Finally create a measure.

count 1 = CALCULATE(COUNTROWS('NewTable'),FILTER('NewTable','NewTable'[Condition]=1))

specific value = VAR _condition_all = CALCULATE(COUNTROWS('NewTable')) RETURN [count 1]/_condition_all

The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Distinct Countx.pbix33 KB

3 Replies

amitchandak
Super User
4 years ago
Anonymous , Try like

calculate(distinctCOUNT(Table[ProjectId]), filter(Table, Table[ Met/Not Met] = "Met"))

Anonymous

Not applicable

4 years ago

Actually i tried all those steps and it is not matching with the requirement.

Let me rephrase it one more time

Consider below table

Project ID	Met	Not Met	Grand Total	Condition = If (Met = Grand Total), 1,0)
10030-01	2		2	1
10077-03	5		5	1
10093-02	8		8	1
10098-01	9		9	1
1010-01	42		42	1
10119-01	13		13	1
10142-01	5		5	1
10162-01	5	2	7	0
10310-02	7		7	1
10331-04	8		8	1
10352-04	2	2	4	0
10376-01	12		12	1
10379-02	5		5	1

Now i want to count how many 1 and total sum i.e, 11/13

Anonymous
Not applicable
4 years ago
Hi Anonymous ，

Create a new table with the following dax expression.

NewTable = SUMMARIZE ( 'Table', 'Table'[Project ID], "Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Met" ), "Not Met", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Met/not met] = "Not Met" ) )

Then create two new columns based on this.

Grand Total = 'NewTable'[Met]+'NewTable'[Not Met]
Condition = IF('NewTable'[Met]='NewTable'[Grand Total],1,0)

Finally create a measure.

count 1 = CALCULATE(COUNTROWS('NewTable'),FILTER('NewTable','NewTable'[Condition]=1))

specific value = VAR _condition_all = CALCULATE(COUNTROWS('NewTable')) RETURN [count 1]/_condition_all

The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Distinct Countx.pbix33 KB

Forum Discussion

Distinct COuntx

3 Replies

Related content

Distinct CountX?

Distinct countx?

Distinct CountX Help

COUNTX distinct values without CALCULATE

count distinct