Trying to get a count of distinct values in a column

[Anonymous]

Hi All,

Im trying to achieve a COUNTIF of the unique values within a specific column to be filter later in the dashboard.

I can use the formula comfortably in MS Excel (see image below)

But trying to replicate this in power bi I am finding difficult. This is what I had done so far:

COUNTIF for Duplicate ID = 
VAR RowID = fact_food_license[pref]
RETURN
COUNTROWS(
    FILTER(
        ALL(fact_food_license),
        RowID = fact_food_license[pref]
    )
)

The error message I am receiving is: 

A single value for column 'ID' in table 'fact_food_license' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result..

Any assitance would be grateful!

[Samarth_18]

Hi @Anonymous 

You can update your measure code with below code:-

COUNTIF for Duplicate ID = 
COUNTROWS(
    FILTER(
        ALL(fact_food_license),
        fact_food_license[pref] = MAX(fact_food_license[pref])
    )
)

Add your pref column along with above measure on your table visual.

Thanks,

Samarth

[Anonymous]

Thanks for you response Samarth

Your solution is only returning the value: 1.

The anticipating results should be 1, 2 or 3

[ryan_mayu · Accepted solution]

@Anonymous 

pls try

calculate(countrows(fact_food_license),allexcept(fact_food_license,fact_food_license[pref]))