Calculate values by taking into account filters applied on database

[sebastienke]

Hi PBI Community, 

My problem is not that simply to explain without writting a long message. I'll try to give you just enough details but not too much 😀

My problem is the next part of that one :

https://community.powerbi.com/t5/Desktop/How-to-count-quickly-distinct-values-with-a-couple-of-filte...

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

- Article version (A, B, C, etc...) and many others columns on which I want to apply slicers for my report

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 first goal was 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. 

Here is the formula used, and it works :

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

Now, I want in my report a chart illustrating, month by month, the number of distinct articles received with more than one receiving reference. So I have created a measure :

What I want = CALCULATE(COUNTROWS(DISTINCT('Table'[Article ]));'Table'[Distinct number of receiving references]>1)

It works, but with this way, I cannot apply slicers correclty on my report. For example if I want to chose the "Version of article" as slicer, the result might be wrong.

Example : 

Date	Receiving reference	Article	Version	Distinct number of reference for the month
Feb	#11	Apple	A	2
Feb	#12	Apple	A	2
Feb	#12	Apple	B	2
Feb	#13	Tomato	A	1
March	#15	Apple	A	1

My chart will display :

• On February : 1 article received with more than one receiving reference (Apple)
• On March : 0 article received with more than one receiving reference

But if I add a slicer on my report for chosing the "Version", the result will always be the same, no matter my slicer choice (by chosing Version = B, Feb should be 0, but it is still 1)

Any idea how to incorporate my additional filters (=slicers) in my result ?

Any help will really be appreciated, it makes me crazy 😵

Thanks a lot

[MFelix · Accepted solution]

Hi @sebastienke ,

Try the following measure to calculate the values you need:

Count above 1 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table';
            'Table'[Article];
            'Table'[Date];
            "@Count@"; COUNTA ( 'Table'[Article] )
        );
        [@Count@] > 1
    )
) + 0

Regards,

MFelix

[MFelix · Accepted solution]

Hi @sebastienke ,

Create a column with startofmonth for each datapoint (if your date column is already in the date format the measure below needs to be changed), add a calendar table and relate to that startofmonth then add the following measure and use the months in the calendar table for your visual:

Count above 1  + month =
CALCULATE (
    [Count above 1];
    FILTER (
        ALL ( 'calendar'[Date] );
        'calendar'[Date] <= MIN ( 'calendar'[Date] )
            && 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
    )
)

Check PBIX file attach.