How to count quickly distinct values with a couple of filter ?

[sebastienke]

Hi all,

I have found 2 (partial) solutions to my problem but I don't like them.... Hope I can find some help 🙂

I have a big database (hundreds of thousands of lines) corresponding to articles received in a warehouse. To simplify it, let's say that I juste have those colums :

- Date of receiving

- reference number of the receiving

- Article

NB1 :

1 Receiving reference usually concerns many articles (let's imagine it is a truck delivering many articles) 

NB2:

I can have more than once the same line repeating, it means that I have received more than once the same article with the same receiving reference.

My goal is to create a column in my database calculating, for each line, the distinct number of receiving references received in the same month for the same article. 

Track 1 : 

This formula works but it is really really realy slow and I want to avoid it.

What_I_want = CALCULATE(DISTINCTCOUNT('TABLE'[Receiving_number]);filter('TABLE';earlier('TABLE'[Article])='TABLE'[Article]);filter('TABLE';earlier('TABLE'[Month])='TABLE'[Month]))

Track 2 : 

I tried to create another table :

NewTable=

SUMMARIZE('TABLE';'TABLE'[Article];'TABLE'[Month];"Distinct_Count_that_I want";DISTINCTCOUNT('TABLE'[Receiving_number]))

I have the number that I want, but this table is not linked to the rest of the data that I want to use in the table "TABLE" (invoice#, vendor, provider, order date, ....) in order to filter the chart that I want to create.

If you can help me, it would be really relly appreciated, because it is a real brain steamer.

Thanks a lot 😉

[Icey · Accepted solution]

Hi @sebastienke ,

What about this?

What_I_want 2 = 
CALCULATE (
    COUNTROWS ( DISTINCT ( 'Table'[reference number of the receiving] ) ),
    ALLEXCEPT ( 'Table', 'Table'[Article], 'Table'[Month] )
)

I used COUNTROWS and DISTINCT instead of DISTINCTCOUNT, referring to this blog: Analyzing the performance of DISTINCTCOUNT in DAX.

Best Regards,

Icey

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

[sebastienke]

Thanks a lot @Icey , it works.

However, I'm not sure to understand the logical of ALLEXCEPT. 

Its description is :

Removes all context filters in the table except filters that have been applied to the specified columns.

but I am not sure to understand the "filter that have been applied". Does the fact to write

 ALLEXCEPT ( 'Table', 'Table'[Article], 'Table'[Month] )

 mean that Power BI will consider for each line of my database the related "article" and the "month" as value for the filter ?

Actually I also have found another solution by improving my track number 2. After having created antoher table with :

NewTable=

SUMMARIZE('TABLE';'TABLE'[Article];'TABLE'[Month];"Distinct_Count_that_I want";DISTINCTCOUNT('TABLE'[Receiving_number]))

I simply linked each line of my initial table to this new table with the formula : 

What_I_want= LOOKUPVALUE('NEWTABLE'[Distinct_Count_that_I want];'NEWTABLE'[Article ];'TABLE'[Article ];'NEWTABLE'[Month];'TABLE'[MONTH])

Thanks a lot !