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 productlocation unique_product_count
12310/1/2020a 1 
12310/1/2020a 2 
12310/1/2020a 3 
12311/1/2020b2 
12311/1/2020b3 
12311/1/2020b4 
12312/1/2020b1 
12312/1/2020b2 
12312/1/2020b4 

 

We want to identify for a given date range (based on a date slicer in the report) which account(s) have more than one unique product
In the above example, if the date slicer is between 10/1/2020 and 12/31/2020 the unique_product_count should be 2 for all rows.
If the slicer is between 11/1/2020 and 12/31/2020 the unique_product_count should be 1 for all rows.
if the slicer is between 10/1/2020 and 11/15/2020 the unique_product_count should be 2 for all rows.

 

Thanks in advance.

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
     

7 Replies

  • 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

    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