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

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'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.