Forum Discussion

ssingh33's avatar
ssingh33
New Member
5 years ago
Solved

How to dynamically count unique values in a table visual

Hi, We are trying to create a power bi report which counts unique products based on account within a dynamic date range. account  date  product location  unique_product_count 123 10/1/20...
Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey ssingh33 ,

     

    I think that description helped me to understand your result better 😊

    Try that version:

    Unique Products =
CALCULATE(
    DISTINCTCOUNT( mytable[product] ),
    ALLEXCEPT(
        myTable,
        myTable[Account],
        myTable[Date]
    ),
    ALLSELECTED( myTable[Date] )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

Hey ssingh33 ,

 

I think that description helped me to understand your result better 😊

Try that version:

Unique Products =
CALCULATE(
    DISTINCTCOUNT( mytable[product] ),
    ALLEXCEPT(
        myTable,
        myTable[Account],
        myTable[Date]
    ),
    ALLSELECTED( myTable[Date] )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi selimovd 
This works for me, except that distinctcount does not count the 'object' with an empty value.

In other words: A location has 100 objects and every object has a value column. I want to know how many objects I have, even when this value column is empty. But I want to have it dynamic, because some objects are irrelevant based on a filter (like when an object only shows midnight values, it does not belong in the midday objects). 
The concrete issue is that I wanted to compare corresponding objects from different locations, but 1 small location did not show that object (the values show how many issues that object had in the past year).

 

I might decide to use the non dynamic count if this does not work.

 

Here is my Measure (TagCode being all the unique objects from all locations):

Count_Object_All =
CALCULATE(
    DISTINCTCOUNT(L_PD_Totaal[TagCode]),
    ALLSELECTED(L_PD_Totaal[TagCode])
)
 
Thanks, Gert