Two distinct counts in visuals that do not sum the values

[Anonymous]

I have the following request:

- Number of unique customers that ordered a certain number of unique products; how many customers ordered 2,3,4,5 etc. products.

The problem I am facing is that it should be filterable by various dimmensions. So for example the same product can be part of two different "order statuses". If the filter is apply on one status it will be counted as 1, which is correct, but if it is not applied it will be counted as two, wich is not correct since it is the same product. No matter if I group this information in SQL or in PBI (custom table with distinct column values for product by using SUMMARIZE) I face the same problem.

Therefore I need somehow to make Power BI display two unique counts without making their sum in the visual.

I have tried to select the AccountID (customer's ID) and ProductId and then from the visual select Count(Distinct), as well as to create measures like this:

# Distinct Products = DISTINCTCOUNT( Fact_OrderLine[ProductId] )

No matter if I use two measures or a measure and a column with Count(Distinct) selected from the visual, the result is the following:

And my desired result should look like this:

Count of AccountId	# Distinct Products
100	4
150	3
145	2
3098	1

Thank you!

[v-stephen-msft]

Hi @Anonymous ,

I think you may try to add a AccountID column and select Don't summarize.

It will be a lot easier for me to help you if you just share your PBI file.  Share the download link.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Anonymous 

Can you please share some sample data?

To what I can understand you want to have a count of the of the customers that have ordered 4 products so believe that you need to create a disconnected table with the values 1, 2, 3,... and use it to make a filter of the values you are counting so in the end you would have a calculation similar to this one.

Has you can see on the table below Customer 1 has 3 distinct items, Customer 2 has 1 and customer 3 has 4

I created a table with the following code:

Count Products = GENERATESERIES(1; 5; 1)

You can change the 5 by a different number.

Now create the following measure:

Calculation of customers per number of products = 
VAR temp_table =
    SUMMARIZE (
        'Table';
        'Table'[ID];
        "@CountProducts"; DISTINCTCOUNT ( 'Table'[Product] )
    )
RETURN
    COUNTROWS (
        FILTER (
            temp_table;
            [@CountProducts] = SELECTEDVALUE ( 'Count Products'[Value] )
        )
    )

Result below:

If you want to have the values withouth number s just add +0 to the end of the measure.

Check PBIX file attach (October version of PBI).

[Anonymous]

@MFelixthank you very much.

That worked great. The only thing I had to modify was to put MIN instead of SELECTEDVALUE in the DAX. As I have understood from others SELECTEDVALUE does not work with SQL Server Analysis Services.

[MFelix]

Hi @Anonymous ,

Does the SELECTEDVALUE appears to you when you try to write the formula?

Be aware that some DAX syntax don't work in some type of connections but they are catching up on some things and adding support to those formulas.

But the use of MIN or MAX is similar so no issue

[Anonymous]

@MFelixthe SELECTEDVALUE is not recognized by DAX in the case of our connection to SSAS. It is not colored in blue as any other function would be and if I still put it there it gives me an error like this one: SELECTEDVALUE is not a valid function.

I am glad it worked with MIN.

Thank you again for the help.