Value and Distinct function giving same result

[Anonymous]

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]))

[v-kelly-msft · Accepted solution]

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!

[Tahreem24]

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

[amitchandak]

@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])