How to dynamically count unique values in a table visual

[ssingh33]

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/2020	a	1
123	10/1/2020	a	2
123	10/1/2020	a	3
123	11/1/2020	b	2
123	11/1/2020	b	3
123	11/1/2020	b	4
123	12/1/2020	b	1
123	12/1/2020	b	2
123	12/1/2020	b	4

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.

[ssingh33]

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]

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

[selimovd]

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]

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 · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

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

[ssingh33]

That works. 

Thanks for your help!