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 ,

 

if I understood the requirements right, the following measure should give you the desired result:

Unique Products per =
VAR vFilterTable =
    ADDCOLUMNS (
        SUMMARIZE ( myTable, myTable[account], myTable[product] ),
        "@AmountProducts", CALCULATE ( DISTINCTCOUNT ( mytable[product] ) )
    )
RETURN
    SUMX ( vFilterTable, [@AmountProducts] )

 

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

 

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

 

  • 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!