Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sebastienke
Helper III
Helper III

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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

17 REPLIES 17
Ashish_Mathur
Super User
Super User

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/
MFelix
Super User
Super User

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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 !

 

 

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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 !
 
 

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

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

 

 

 

 

Hi @sebastienke ,

 

Not really sure if I'm uderstanding your question but if you place a slicer for the month and another for the articles that should give you the values you need.

 

Can you please elaborate a little bit more on what is the interaction you are trying to achieve?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

Alle the solutions we've talked in this topic allow me to :

each month, count the number of articles which have more than 1 distinct receiving references during a period of two months (month n and n-1)

So, if I have received apples 3 times in January, and 0 in February, and if I take a look on February, our formula will take into account the apples. But because apples have not been received in February, I don't want to take them into account. 

 

Example : 

Here is the database :

 

DateReceiving referenceArticleVersion
18PeachesA
19PeachesB
210AppleB
211AppleA
211AppleA
211TomatoA
212TomatoB
313TomatoA
314TomatoA
415AppleA

 

Here is the result of the current calculations :

 

DateResultComments
1?last month data missing
23peaches, apple, tomato
32apple, tomato
41tomato

 

Here is the result I'd like to obtain : 

DateResultComments
1last month data missing 
22apple, tomato
31tomato
40nothing

 

Thanks a lot 🙂

 

Hi @sebastienke ,

 

Sorry for asking this but based on your measure and taking into account that you are filtering the counts above 2 the final result you presente is incorrect:

 

I did a summarize table with your measure  and got the following table below

 

 

Article @Count2+2m@ Month
Apple 2 2
Apple 2 3
Tomato 2 3
Tomato 2 4

Peach

2 4

 

so final result would be:

1 - 0

2 - 1 product (apple)

3 - 2 product (Apple, tomato)

4 - 3 product (Apple, Tomato, peach)

5 - 2 product (Tomato, Peach)

 

 

Can you confirm this analysis?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix 

I'm not sure you used the last example of my last topic.

The first picture is the result of my measure (the one which is wrong and that I want to correct), when displayed month by month. It corresponds to my last example. The others pictures are the different parameters used to let you know.

 

1.PNG

 

2.PNG

 

3.PNG4.PNG

 

Thank you again 

 

 

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



It works perfectly 🙂

 

Many Many thanks for your time @MFelix !

Hi @sebastienke ,

 

Can you please confirm what is the measure you are currently using for the calculation of the quantities?

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix 

 

Here is the measure :

 

Received 2+ in 2 months  =
CALCULATE (
     COUNTROWS (
          FILTER (
               SUMMARIZE (
                    'Table;
                    'Table'[Article ];
                    "@Count2+2m@"; COUNTROWS(DISTINCT('Table'[Receiving reference]))
               );
               [@Count2+2m@] >= 2
          )
     );
     FILTER (
          ALL ( 'Calendrier'[Date] );
          'Calendrier'[Date] <= MIN ( 'Calendrier'[Date] )
          && 'Calendrier'[Date] >= MINX( 'Calendrier'; DATEADD ( 'Calendrier'[Date]; -1;MONTH ) )
     )
)
 
I guess I need another FILTER function in my CALCULATE, but I' m not sure how to apply it. 
 
Thanks a lot for your time

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.