Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Value and Distinct function giving same result

I am using two tables "Category" and "Product"

Below is the output which I am getting:

Ideally Distinct function should calculate the value excluding the blank. But here Value and Distinct is giving me the same result. 

Here is the definition which I used:

Products DISTINCT = COUNTROWS(DISTINCT('Product'[Product]))
Products VAL = COUNTROWS(VALUES('Product'[Product]))
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Modify measure Products DISTINCT as below:

    Products DISTINCT = IF(MAX('Product'[Category]) in FILTERS(Category[category Id]),CALCULATE(DISTINCTCOUNT('Product'[Product])),BLANK())

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You are getting blank because your category table does not have all categories. The calculation is correct

     

    you can also try

    Products DISTINCT = DISTINCTCOUNT('Product'[Product])

  • Tahreem24's avatar
    Tahreem24
    Super User
    5 years ago

    Anonymous ,

    It works perfect. As you dont have blank records in your Product column. If you count for Category so it may give you right result because you have blank records in Category.

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Anonymous ,

     

    Modify measure Products DISTINCT as below:

    Products DISTINCT = IF(MAX('Product'[Category]) in FILTERS(Category[category Id]),CALCULATE(DISTINCTCOUNT('Product'[Product])),BLANK())

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!