Count or distinct count

[UK_User123456]

Hi Community,

I have a column in my data that has a type of "text" but when I try to do a count or distinct count of the data, it just doesnt work. Can anyone help?

The example is as follows:

Code

4110 - Uni

4112 - Coll

4131 - Store

4121 - STA

4110 - Uni

4110 - Uni

4110 - Uni

4121 - STA

4131 - Store

4131 - Store

4121 - STA

4131 - Store

4131 - Store

4121 - STA

The output should be:

Code	Count
4110 - Uni	4
4112 - Coll	1
4131 - Store	5
4121 - STA	4

Any help would be greatly appreciated.

TIA

[Anonymous]

Hi @UK_User123456 ,

Don't know what steps you are following .However when I tried with your example in a table Visual, I get the output as expected:

see screnshot below:

Thanks,

Tejaswi

[UK_User123456]

But would this work if it was referenceing a measure that was calculated?

[UK_User123456]

I have a calculated measure that is based upon a ranking column in my base data, I then wanted to count the number of times those codes appear.

[UK_User123456]

The calculation in my data set is 

Code Rank =

CALCULATE(

MIN('Table 1'[Code] ),

'Table 1'[Code Rank (ASC)] = 1

)

I then wanted to do a distinct count or a count based on the ranking that equals to 1 

[Anonymous]

Hi @UK_User123456 ,

I am now confused and not very much clear with your requirements.

Your first post says you want the count of codes, but now it says it is based on different data.

Could you be more specific or provide some sample data?

With your sample data and specific requirements it would be easy for us to resolve your issues ASAP.

Thanks,

Tejaswi

[UK_User123456]

As with my previous post, I have created a column within my data table set that gives me the ranking of the code by the date as follows:

ID Rank (ASC) =

RANKX(

FILTER(

'table 1',

'table 1'[ID] = EARLIER('table 1'[ID] ) &&

'table 1'[Date] = EARLIER('table 1'[Date] )

),

'table 1'[Date],

,

ASC,

DENSE

)

And then I have a measure which gives me the output of the "code" based on which rank I want to output which is:

Code Rank =

CALCULATE(

MIN('Table 1'[Code] ),

'Table 1'[Code Rank (ASC)] = 1

)

I just need to be able to do a count of the output based on the ranking I choose, so say I opt to only show ranking 1, it will display all the codes that have a rank of "1" against it.

example dataset below:

ID	Date	Code		Power Bi Measure Ranking
1	03/01/2017	4110 - Uni		1
1	02/01/2017	4110 - Uni		1
2	01/05/2019	4112 - Coll		1
3	02/02/2018	4131 - Store		1
4	01/06/2018	4121 - STA		1
1	12/01/2017	4110 - Uni		2
1	15/07/2018	4110 - Uni		3
1	21/02/2019	4110 - Uni		4
4	12/08/2018	4121 - STA		2
3	12/02/2019	4131 - Store		2
3	15/05/2019	4131 - Store		3
4	13/02/2019	4121 - STA		3
4	02/06/2018	4121 - STA		1
3	17/06/2019	4131 - Store		4
3	30/06/2019	4131 - Store		5
4	16/07/2019	4121 - STA		4
2	01/07/2019	4112 - Coll		2

Potential final output based on ranking 1:

Code	Count of Code
4110 - Uni	2
4112 - Coll	1
4131 - Store	1
4121 - STA	3

I am now thinking that this cannot be done based on creating a ranking column in my dataset. 

TIA

[v-xuding-msft]

Hi @UK_User123456 ,

Have you got the column of  Power Bi Measure Ranking using the formula of ID Rank (ASC)?  If so, you can create a measure to count code.

Measure = CALCULATE(COUNT('table 1'[Code]),FILTER('table 1','table 1'[ID Rank (ASC)] = 1))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richbenmintz]

Hi @UK_User123456 

The following measures should give you the counts you are looking for

Count = CountRows('table')

Distinct Count = DISTINCTCOUNT('table'[column])

I hope this helps,

Richard