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
     
ssingh33's avatar
ssingh33
New Member
5 years ago

Hi,

 

Thank you for the quick response. We tried the measure but it returns a value of 1 when there are 2 unique products. The measure you give makes sense, but the way PowerBI calculates the unique products doesn't seem to take into account all the valuse of the dataset. 

 

 

From the above screenshot, the Unique Products per column should have a value of 2 for all the rows.

 

Thanks

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
 
  • ssingh33's avatar
    ssingh33
    New Member
    5 years ago

    That works. 

    Thanks for your help! 

  • 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