cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sebastienke Regular Visitor
Regular Visitor

Calculate values by taking into account filters applied on database

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 : 

DateReceiving referenceArticleVersionDistinct number of reference for the month
Feb#11AppleA2
Feb#12AppleA2
Feb#12AppleB2
Feb#13TomatoA1
March#15AppleA1

 

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

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

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


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

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.


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

Hi @sebastienke ,

 

You need to have an extra filter so that you can deselect the values that are not present in the current month so the measure should be redone to this:

 

Received 2+ in 2 months =
IF (
    MIN ( 'calendar'[Date] )
        = CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) );
    "Last Month data Missing";
    CALCULATE (
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    'Table';
                    'Table'[Article];
                    "@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
                );
                [@Count2+2m@] >= 2
            )
        );
        FILTER (
            ALL ( 'calendar'[Date] );
            'calendar'[Date] <= MIN ( 'calendar'[Date] )
                && 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
        );
        FILTER (
            SUMMARIZE (
                'Table';
                'Table'[Article];
                "@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
            );
            [@Count2+2m@] > 0
        )
    ) + 0

 

For the text part you need to add the following measure.

 

Articles selected =
SWITCH (
    TRUE ();
    MIN ( 'calendar'[Date] )
        = CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) ); "";
    [Received 2+ in 2 months] = 0; "Nothing";
    CONCATENATEX (
        FILTER (
            SUMMARIZE (
                'Table';
                'Table'[Article];
                "@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
            );
            [@Count2+2m@] >= 2
        );
        'Table'[Article];
        ","
    )
)

 

Believe this get's the result you need.

 

Check PBIX file attach.


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

17 REPLIES 17
Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

Hi @sebastienke,

You need to add the columns where you need the slicers to the all except function.

Check the documentation on the ALL EXCEPT function for a better understanding.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User IV
Super User IV

Re: Calculate values by taking into account filters applied on database

Hi,

Share a simple dataset and show the result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sebastienke Regular Visitor
Regular Visitor

Re: Calculate values by taking into account filters applied on database

Thanks @MFelix  for the proposing, but it doesn't solve my problem. 

If I add the "version" to the ALLEXCEPT function, the problem is similar to the first one. Here is what I have :

 

BEFORE :

before.PNG

AFTER:

after.PNG

 

So when I use my measure to calculate, month by month, the number of distinct articles received with more than one receiving reference, the result is right, no matter the version slicer I choose.

 

measure.PNG

 

Slicer = all

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

Slicer = Version A

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

Slicer = Version B

  • On February : 0 article received with more than one receiving reference
  • On March : 0 article received with more than one receiving reference

But it works only for this example. 

Let's say I have the following database instead of the first one (1 line is missing) :

 

new.PNG

In that case my result will be ok when I choose slicer = A or slicer = B but not when slicer = all (result will be 0 for February instead of 1)

 

I think my error is to calculate the column "Distinct number of receiving reference" directly in my database, for each line. I think I need to incorporate this in a measure, but I don't know how to do it. Any idea ?

 

Thanks for you help !

 

 

 

Microsoft V-lianl-msft
Microsoft

Re: Calculate values by taking into account filters applied on database

 

After my test, it seems that there is no problem you said.

 
test_distinct.PNG
Here is the test pbix
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

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


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

sebastienke Regular Visitor
Regular Visitor

Re: Calculate values by taking into account filters applied on database

Many thanks @MFelix 

 

It is so obvious now that I know the solution.... Summarize.... that was the key.

 

Actually I did a little bit correction to obtain the result I wanted, but the idea was here. 

 

Count above 1 = COUNTROWS(FILTER(SUMMARIZE(
'Table';'Table'[Date];'Table'[Article];"@Count@";COUNTROWS(DISTINCT('Table'[Receiving reference]
)));
[@Count@] > 1)) + 0
 
Thanks to all for your help, great community !
 
 
sebastienke Regular Visitor
Regular Visitor

Re: Calculate values by taking into account filters applied on database

Hi @MFelix

 

Can you help me one more time abouth the same topic ?

 

If I want to use the same measure in my report to illustrate the number of distinct articles received with more than one receiving reference not month by month, but for 2 rolling months (result of March = result for March + February )

 

Example

DateReceiving referenceArticleVersion
Feb#11AppleA
Feb#12AppleA
Feb#12AppleB
Feb#13TomatoA
March#15TomatoA

March

#15

AppleA
March

#25

TomatoA
March

#28

AppleB
April

#35

Peach

A

April

#37

PeachB
April

#38

TomatoB
April

#40

TomatoB

 

In that case the result will be (without any slicer on the version): 

 

Date

number of distinct articles received with more than one receiving reference for 2 rolling months

FebJanuary data missing + 1
March3 (1 for Feb and 2 for March)
April4 (2 for March and 2 for April) 

 

I tried different things but without any success. 

Do you have any idea how to do it ?

 

Thanks a lot for your hep

 

Super User III
Super User III

Re: Calculate values by taking into account filters applied on database

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.


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

sebastienke Regular Visitor
Regular Visitor

Re: Calculate values by taking into account filters applied on database

BRILLIANT @MFelix 

I would have never thought about that solution !

 

Last question : 

If I want to add the filter that select only the articles receveid in the month observed, how do I do that ?

For example :

If I have :

  • in January : 2 distinct receiving for tomatos and 3 distinct receiving for apple.
  • in February : 1 distinct receiving for tomatos and 3 distinct receiving for peaches

And if I want to observe February, I should see as result that I have receveid just 2 articles more than once for the 2 rolling months: tomatos with 3 distinct receiving and peaches with 3 distinct receiving, but not apples because this sku was not received in February (although it was received twice in January). 

 

That would be my last question. 

Many many thanks for your help. 

 

 

 

 

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors