cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User III
Super User III

Re: Testing how Filter works

Hi @gvg,

 

No because you are summing the total sales the SalesX is a column to filter out the information you need it's not included in the calculation as a variable.

 

To make it that way you should have the temporary table calculated as a variable and then use it on the SUM.

 

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

gvg Member
Member

Re: Testing how Filter works

Ok, good to know. Thanks @MFelix !

gvg Member
Member

Re: Testing how Filter works

Hello @MFelix,

 

This what you showed is a very useful DAX technique which I did not know before and I'd like to get deeper into it. If I, for example, have many more fields in Table1, how can I filter on some other field in Table1 that is not summarized (i.e. not included in SUMMARIZE function)? When I try to do it, Power BI would not allow to enter any field, that is not included in SUMMARIZE function. I am looking to do something like this :

 

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "salesX"; SUM ( Table1[Sales] )
        );
        [salesX] < 70 && Table1[NetQuantity]> 100
    )
)

I cannot put field NetQuantity in SUMMARIZE as summarization by it will give me wrong result for SalesX.

 

gvg Member
Member

Re: Testing how Filter works

OK, found a way out. You just add another FILTER to CALCULATE:

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "salesX"; SUM ( Table1[Sales] )
        );
        [salesX] < 70 
    ),
    FILTER (Sales, Table1[NetQuantity]> 100)
)
Super User III
Super User III

Re: Testing how Filter works

HI  @gvg,

 

Sorry for not responding to you I'm travelling in bussines, small hours to respond in the forum.

 

Glad you could work it out.

 

 

Regards,

MFelix


Regards

Miguel Felix


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

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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