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 selimovd 

 

The solution works for Import mode, but not when we use it in a report that has Direct query. When we use Direct Query, the value for distinct product is always 1.

Is this an issue with the behavior of the ALLEXCEPT and/or ALLSELECTED functions?

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hello ssingh33 ,

     

    yes, both ALLEXCEPT and ALLSELECTED won't work with DirectQuery.

    Is there a specific reason you are using DirectQuery? It has a lot of disadvantages and is most of the times not needed.

     

    Best regards

    Denis