Forum Discussion

UK_User123456's avatar
UK_User123456
Resolver I
7 years ago

Count or distinct count

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:...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
UK_User123456
Resolver I
7 years ago

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:
 
IDDateCode Power Bi Measure Ranking
103/01/20174110 - Uni 1
102/01/20174110 - Uni 1
201/05/20194112 - Coll 1
302/02/20184131 - Store 1
401/06/20184121 - STA 1
112/01/20174110 - Uni 2
115/07/20184110 - Uni 3
121/02/20194110 - Uni 4
412/08/20184121 - STA 2
312/02/20194131 - Store 2
315/05/20194131 - Store 3
413/02/20194121 - STA 3
402/06/20184121 - STA 1
317/06/20194131 - Store 4
330/06/20194131 - Store 5
416/07/20194121 - STA 4
201/07/20194112 - Coll 2

 

Potential final output based on ranking 1:

 

CodeCount of Code
4110 - Uni2
4112 - Coll1
4131 - Store1
4121 - STA3

 

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

 

TIA

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    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.